question

UT6 avatar image
UT6 asked

Do you recomend restoring master database in DR Server in case of disaster

Hi There, Do you recomend restoring master database in DR Server in case of disaster being occuring. If its the logins and user objects in master, they can be scripted out and sent to DR Site from time to time. Say if we use DB mirroring or log shipping, we do script out our logins from time to time anyway right? Since master database stores various other information like Database names, locations and encryption passwords. Would it may life difficult or easier? I know that for master DB restore the Version/build of SQL Server on Target/DR should be same as Primary Server. I want to discuss what are benefits and losses of restoring master DB to DR Site. Thank you.............
sql-server
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Fatherjack avatar image
Fatherjack answered
You first of all need to define your criteria of what constitutes a disaster. Backups of Master will only really pay off if you are rebuilding the original server - possibly the Master datafiles have become corrupt or the hard drive where they were has been destroyed but all other parts of the server are OK. If you are moving to a new server - ie your 'disaster' means the original server is beyond recovery then there should be a Master database already on the standby server and having a backup of the other database is very little use. Having a valid backup of all the user databases readily accessible is what you need and what will get your systems back on line fastest.
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

In that case forget trying to restore Master to your SDC, this will just cause problems - distracting you from getting the user databases restored and online. restoring Master is tricky enough let alone when there is fallout from a disaster all around you. If you are using Mixed Authentication then you can get your Server Principals created, as a head start on the security restore process but in reality you just need to test it out and note the places where you can put things in place to speed up your time to recovery.
1 Like 1 ·
Sorry, by the word Disaster I mean whole PDC(Primary Data Center) is gone. We are only left with SDC(Seconday Data Center) somewhere with offsited and available backups of both system and user databases.
0 Likes 0 ·
What about situation where you have encryption enabled(TDE,other ones as well)? would master Database restore makes any things better? And sorry but what other information is stored in master database and what kind of issues can be encountered if we chose to restore master database to DR Site? Thank you
0 Likes 0 ·
Restoring Master is not like restoring any user database - http://msdn.microsoft.com/en-us/library/ms190679.aspx. This isn't the sort of thing that I would be at all interested in doing while I have the managers of all other systems on my back asking when their systems will be online. Surely your DR server is already encrypted? It would have it's own settings, keys etc that mean there is nothing on the primary that would be any use.
0 Likes 0 ·
Thank you....
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.