Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Minesalatte
Starting Member
10 Posts |
 Posted - 2007-03-09 : 07:56:33
|
| OS: Windows 2003 Standard EditionMS SQL: Standard Server 2005I 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.Disaster:Office burnt downRecovery: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_backupStart MS SQL in single user mode, restore master database using .bak.Start MS SQL in normal mode, restore other system databases using .bakRestore user databases using mssql_backup deviceTesting: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 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-03-09 : 09:24:53
|
| you need to create a restore script...search the weblogs, there are some posts on restores, for starters--------------------keeping it simple... |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-09 : 09:53:25
|
| "is there a command that would in effect be able to restore all databases using the backup device?"This may help:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE%20syntax%20/%20examplemake 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 DBCC CHECKDB('MyDatabase') WITH NO_INFOMSGSto 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.Kristen |
|
|
|
Minesalatte
Starting Member
10 Posts |
Posted - 2007-03-12 : 08:27:25
|
| Hi,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, |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-12 : 08:41:34
|
"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  Kristen |
|
|
|
|
|
|
|
|
Distaster Recover