We wrote our own scripts because we have multiple databases and wanted to be
able to schedule a single job using a single procedure that put the backup
files in the correct locations on our NAS box. The stored procedure we use
is attached to this email although I don't know if it will come through the
mailing list or not. If the file doesn't attach, you can see the stored
procedure at this URL:

http://wwwmain1.intouchavl.com/lazydba/

The gist of it is that the procedure builds a filepath that consists of the
server name and the database name. The file name is then build based on the
day of the week and the backup type (full or transaction). The resulting
filepath + filename are used to call DUMP DATABASE or DUMP TRANSACTION to
the filename that was generated. We run the exact same script on all of our
database servers and just have to create the matching directory structure on
our network path.

The restore procedure is much easier. Assuming you know the filename you
want to restore, you call something like this:

restore database mydatabase
from disk='\\networkserver\filepath\dbfile.dat'
with replace, stats=10

If you want to restore the main database with and apply transaction logs
after that, make this call instead:

restore database mydatabase
from disk='\\networkserver\filepath\dbfile.dat'
with replace, norecovery, stats=10

And then call this for each transaction log to apply after it:

restore log intouch_comms
from disk='\\networkserver\filepath\logfile.dat'
with norecovery, stats=10

The LAST log file must be called with RECOVERY instead of NORECOVERY or the
database will be left in a loading state and you won't be able to use it.

Hope this help.

- Jim

-----Original Message-----
From: tessier.[Email Address Removed] [mailto:tessier.[Email Address Removed]
Sent: Wednesday, April 28, 2004 7:46 AM
To: LazyDBA.com Discussion
Subject: Backup/restore procedures.

Hi all.
I'm considering writing backup and restore procedures. Any of you DBAs have
procedures that you would be ready to share or know about resources I could
look at to start with. Any help will be greatly appreciated.

Thanks

Pierre Tessier
IT Applications Specialist | Spécialiste des applications de TI Information
Management and Technology Directorate | Directorat de la gestion de
l'information et de la technologie Corporate Services Branch | Direction des
services ministériels Department of Finance and Treasury Board of Canada
Secretariat | Ministère des Finances et Secrétariat du Conseil du Trésor du
Canada Ottawa, Canada K1A 0G5
613-996-8958 | Tessier.[Email Address Removed] | facsimile/télécopieur 613-995-5235
MS Sql Server LazyDBA home page