THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

James Luetkehoelter

Nearly any SQL topic presented at times in a slightly eclectic manner.

Restoring Master - beyond the technical

Thank Greg Low for reviewing my book and pointing out one of the copy-edit issues in it (I wasn't involved in that process by choice and schedule, a mistake I regret). As Greg pointed out in his post, one of the most imporant aspects to dealing with backup and recovery is restoring Master. In my book, it makes reference to a screenshot that should show a basic restore statement in action - instead it is a screenshot of setting the permissions for instant file initialization?? Silly James, I don't know how that got in there, but was probably my fault to start with (and mine for not seeing it first).

The best resource for is BOL - seriously, the best documentation for any software product anywhere - MSDN also has a copy. SQL Server has to be started in single user mode (sqlserver.exe -m), then a simple restore statement works:

RESTORE DATABASE MASTER from disk='D:\SQLbackup\master.bak'

You're forced to restart - but what's next. Guess what happens to your user databases if they're in a new location (such as a new drive letter or directory) - in SQL 2000, they're all suspect. In 2005 they're just unable to go through recovery because the files can't be found and are unavailable. Scary, but you can always restore them with a "with move" statement to change the location (again, BOL people for syntax - if you don't live there, you should; memorizing syntax should come second to understanding process).

Now what happens if you happen to try to restore to an instance that isn't the same build level? With all of the post-SP2 cumulative updates, if you have a significant installation base it is quite possible (I've been there). It isn't pretty. There is a KB article on that, but the better question might be - before I start restoring this master.bak file, do I know how recent it is? Is it on the same build? Of course its always recommend that whenever a change is to the instance itself, especially a patch, Master should be backed up. This is one of the practical things I talk about in my book - in a disaster situation, the key is not to panic and get your bearings (it drives CEOs crazy if they see you trying to be calm though :) ). This particular issue is also relavant to the Model and MSDB databases.

We also know what happens with users in other databases when Master is restored. If they are SQL authentication logins, they can be out of sync with the user databases, requiring either a synchronizing script to move the secure IDs randomly created (SID) between the Master and user databases or with a script like sp_change_users_login (BOL people if you don't know it - if you do, you've earned a DBA stripe :) ). Common thought is that if Windows Authentication is used, this mismatched users issue doesn't occur because the SID is the actual Windows SID, so it would be the same in the Master database in the logins as well as all user databases. Dangerous line of thought!! What if you are restoring to a new domain? Guess what, the SIDs mismatch. Again, take time to think about the situation before diving in; once you restore Master, it isn't pretty to clean up if you have to...

So what's your Master horror story?

Published Monday, July 14, 2008 5:58 PM by James Luetkehoelter



Tim M. Hidalgo said:

My master db horror story...

when you need to restore server A master db on server B where server B has a master db set to a different file path >.<

September 15, 2009 6:12 PM
New Comments to this post are disabled

About James Luetkehoelter

I am passionate about what I do - which is DBA, development, IT and IT business consulting. If you don't know me, haven't met me or have never heard me speak, I'm a little on the eccentric side. One attendee recently described me as being "over the top". Yup, that about says it - because I only speak on topics that I'm passionate about.
Privacy Statement