SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Disaster recovery best practices - system databases


Disaster recovery best practices - system databases

Author
Message
Matthew Darwin
Matthew Darwin
SSC-Addicted
SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)

Group: General Forum Members
Points: 471 Visits: 875
I'm currently working on a new design for DR for my SQL Servers; I have four servers and a total of seven instances, three servers only have the default instance, whilst one has three instances.

In my DR environment, I only have two servers availabe, so I'll be squeezing the seven instances onto those two servers.

At this point, I'm wondering what the best practices are for the system databases, in particular master and msdb, and restoring those onto a different server and possible from a default instance to a named instance (I don't know whether this makes a difference at all?)

I'm aware that the server builds need to be the same to restore the master dbs; is there anything else I need to be aware of?

What I'm wondering is whether it's worth performing the restores, or whether I'm better generating scripts to recreate the server level objects that are held in the master db, and the SQL Server Agent jobs in msdb etc and then running those in the event of a DR switch?

Follow me on twitter @EvoDBA

Check out my blog Natural Selection DBA
SQLSACT
SQLSACT
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2166 Visits: 2931
System databases can be restored as user databases on your DR servers - Just keep track of which ones belong to which servers.

When you have downtime on the Prd servers, copy out the data and log files for each of the system DB's -Keep these on your DR server somewhere

Backup your system DB's regularly - Store the backups somewhere safe - Possibly on the DR server
Matthew Darwin
Matthew Darwin
SSC-Addicted
SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)

Group: General Forum Members
Points: 471 Visits: 875
I'm not sure what good having them restored as user databases does, unless I'm missing something?

All my databases, including system, are backed up regularly and stored in several secure locations (one of which is the DR server itself) so I have that covered.

I'm mostly concerned with ensuring that either the following items exist or can be easily created in the event of a need to failover to DR:-

Logins with relevant permissions
Keys (the database master keys are encrypted by the service master key) - these are all backed up so can be restored
Certificates (some logins are owned by a cert) - all backed up so can be restored
Server scoped triggers
SQL Agent Jobs

If I restore the master and msdb databases onto my DR server as the master and msdb system databases, will I be covered, or will it be fiddly to get it all working? If the latter, I guess having a script regularly generated that will create them all is the way forward?

Follow me on twitter @EvoDBA

Check out my blog Natural Selection DBA
SQLSACT
SQLSACT
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2166 Visits: 2931
I've dealt with corrupter maser databases before - Instead of rebuilding all system databses, which is the fix for master db corruption, I did the following:

Took a previous backup of the master DB, restored is as a user DB on another server, took the MDF and LDF from the restored master DB and replaced the corrupted master's MDF and LDF with those - That worked quite well.
That way, you don't end up having to rebuild all system DB's and then restoring.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search