How to restore Master DB – SQL Server 2008 R2

When you are a DBA it can be almost dull at times, you keep on top of your checks, try to correct any problems that may pop up and generally keep things ticking along. However when disaster strikes, suddenly you are on the spot, and all those hours spent testing restores, and checking backups are up to date pay off as you move into disaster recovery mode.

When restoring a SQL database it can make your job incredibly easier if you restore the master database, especially if your server has been completely rebuilt and you are wanting to keep all your previous configurations.

Remember to do this BEFORE you start restoring all the other other databases. Get it done first.

  1. Start SQL in single user mode (-m as a startup parameter ) – This goes at the begining of the parameters
  2. Fire up SQL Command utility (run SQLCMD from the command line)
  3. Run the restore code – eg RESTORE DATABASE master FROM **backup_file** WITH REPLACE
  4. Remove the parameter and restart SQL Service

Ref: Microsoft

I have personally done this when restoring a mission critical production server and it worked perfectly.

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

1 Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.