| Author |
Topic |
|
keithar2001
Starting Member
3 Posts |
 Posted - 2011-04-20 : 18:17:14
|
| I routinely use scheduled maintenance plans to backup the master, model, msdb and application defined databases for each SQL 2005 database instance on my SQL server. I use the full recovery model and perform a full backup each evening with multiple transaction log backups throughout the business day. The backup files are then replicated to our disaster recovery site. I want to perform a disaster recovery test. I have a second server at the DR site with SQL 2005 installed with the same service pack as my production environment. I know how to properly restore the database to the correct location on the DR server, but I do not know if I should restore the master, model and msdb databases or only the application databases. If the system databases are restored, should they be restored before the application databases? Can someone please advise?Sincerely,Keith RobertsNetwork AdministratorHometown Bank |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2011-04-21 : 03:24:20
|
| restoration of only application databases is enough. Still if you want to restore system databases for test, then sequance doesn't matter.Backup of which system databases is a good practice. follow this linkhttp://msdn.microsoft.com/en-us/library/ms190190.aspx--------------------------http://connectsql.blogspot.com/ |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-04-21 : 04:34:37
|
| If you have logins that you need, or linked servers, jobs, dts or ssis packages you need to either restore the system databases (though I don't recommend restoring master from another server) or script those items from your primary and apply them on the DR box.--Gail ShawSQL Server MVP |
|
|
|
keithar2001
Starting Member
3 Posts |
Posted - 2011-04-21 : 10:52:09
|
| Thank you both for your replies. I have no linked servers, dts or ssis packages. The only jobs I have are the daily backups and weekly maintenance jobs which I can recreate. I believe that the logins are stored in the master database. Is that correct? If so you believe I am better off to script them than to restore the master db to a different server. |
|
|
|
Peter99
Constraint Violating Yak Guru
498 Posts |
Posted - 2011-04-21 : 11:39:33
|
| If you have same server name,build,sp then restore:1. master2. msdb3. user databases |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-04-21 : 12:57:08
|
| Correct, logins are in master.If you script, make sure you script with the SID, or you'll have orphaned logins on the DR server--Gail ShawSQL Server MVP |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2011-04-21 : 14:04:17
|
| This is the little utility that I wrote to script out server information for DR. You can run it with the /nodb switch. It will generate a script of logins along with their role membership.http://scriptsqlconfig.codeplex.com/=================================================Creating tomorrow's legacy systems today. One crisis at a time. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-04-21 : 14:16:20
|
quote:
Originally posted by graz
You can run it with the /nodb switch.
First glance I read that as '/noob switch'  --Gail ShawSQL Server MVP |
|
|
|
|
Disaster Recovery