Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Distaster Recover
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

10 Posts

Posted - 03/09/2007 :  07:56:33  Show Profile  Reply with Quote
OS: Windows 2003 Standard Edition
MS SQL: Standard Server 2005

I am looking to implement a disaster recovery plan for an MS SQL 2005 server, this is what I have done so far but would like the benefit of your experience to provide feedback and help improve the strategy.

MS SQL Server:
I have two maintenance plans, one to backup the system databases to individual backup files (.bak) and one to backup all the user databases to a backup device (mssql_backup) The backup files & device are in a directory (backup) and is backed up using NFS. I keep a copy of the backups on a USB HD which is stored offsite.

Office burnt down

New server, install OS/MSSQL, patch up to same version as burnt server.

Copy .bak files to new server, copy mssql_backup file to same location as previously held i.e. X:\backup\mssql_backup

Start MS SQL in single user mode, restore master database using .bak.

Start MS SQL in normal mode, restore other system databases using .bak

Restore user databases using mssql_backup device


Using the above steps I was able to restore up to the point of "user databases" Getting to this point I realised that there are a number of databases to restore, in my novice ways this seems to be a lot of right-clicking and restoring.

1) is there a command that would in effect be able to restore all databases using the backup device?

2) do I have a working version of the databases to the point of the last backup? Or am I missing something?

3) in my current setup in mind (SQL backup to NFS directory and picked up my backup software) is there a 'better' way I could be doing this which would make the restoration procedure more efficient

Flowing Fount of Yak Knowledge

4110 Posts

Posted - 03/09/2007 :  09:24:53  Show Profile  Send jen a Yahoo! Message  Reply with Quote
you need to create a restore script...

search the weblogs, there are some posts on restores, for starters

keeping it simple...
Go to Top of Page


United Kingdom
22859 Posts

Posted - 03/09/2007 :  09:53:25  Show Profile  Reply with Quote
"is there a command that would in effect be able to restore all databases using the backup device?"

This may help:

make sure that New Machine has the same Drive and Directory structure as Old Machine. Make sure you install the IDENTICAL Collation on New Machine.

You should copy the MDF and LDF files for Master on "New Machine" (with SQL Service stopped) before you attempt too restore Master - just-in-case!

It might be worth also taking a COPY of the MDF and LDF files for Master (with SQL Server shutdown) - you need to do this at least once after installing any Service Pack. If you get any aggro you can try just copying those over the ones in "New Machine".

"do I have a working version of the databases to the point of the last backup?"

You are good-to-go. If your databases are in FULL Recovery Model you should be planning to take TLog backups very frequently (e.g. 10-15 minutes - no sense running then any less often - why lose data you don't have to? After "building burnt down" you are only as good as your last physical copy - so you may want to also copy those TLog backups "off site" as soon as they are made.

If you are UNABLE to restore your Backups you are toast. To prevent that you should trial-restore the backups - for missions critical trial-restore EVERY backup, otherwise once a week or somesuch will do. Do a


to check that each restore is OK.

(Run that on ALL databases after restoring onto New Machine too - no sense having bust databases and not being aware of it for a while whilst you are busy trying to fix everything else that you lost in the fire!

"is there a 'better' way"

Sounds alright to me - but there is a lag between getting the backup onto NFS and then from there on to tape. Make SURE that your daily backups finish BEFORE the Tape backup starts - otherwise your backup will be 24 hours more stale - and if poss immediately copy from NFS off site - particularly the TLog backups (and old FULL backup will do if you have ALL TLog backups since that,a nd the TLog ones will, mostly, be small - so Zip and copy across the Web to some remote machine would help your recoverability.

Go to Top of Page

Starting Member

10 Posts

Posted - 03/12/2007 :  08:27:25  Show Profile  Reply with Quote

Thanks for your information and helpful advice. The link posted is something I have bookmarked and plan to read as much as possible.

I will need to fully check the restore, thanks for the command.

I have my recovery model set to simple, does this change how I backup the TLog?

I'm not backing up to tape, I currently backup to disk and duplicate to an external drive which is then taken out the office. I have future plans to rsync to an external server.

Thanks again,
Go to Top of Page


United Kingdom
22859 Posts

Posted - 03/12/2007 :  08:41:34  Show Profile  Reply with Quote
"I have my recovery model set to simple, does this change how I backup the TLog?"

You don't need to backup the Tlog in SIMPLE Recovery model - just do a Database backup.

You can do a FULL backup, then a number of Differential backups, and then another FULL backup if you like - we do a FULL on Sunday, Differential on each weekday, but purely to reduce disk space requirements for the backups. If you are basically doing your backups to get them on to tape them better to do a FULL backup every time - less chance that a recovery will fail - for example, a broken Sunday backup means that we cannot recover any of the Differentials in that week

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000