SQL Server – Restore Master Database

I have been working toward a SQL Server certification (70-432). This has resulted in me getting quite a good knowledge of how things work. Here is something interesting I have learned:

Steps (this only works if you have a good backup of Master)

1. Stop the SQL Server instance.

2. From a command prompt (must be running the command prompt as administrator), go into the Binn directory of the SQL Server instance and run “sqlservr -m”. This will start up a special single user mode.

3. Start another command prompt as administrator. Run sqlcmd and then perform the restore, something to the effect of: restore database master from backupdevice if you are using preprogrammed backup devices (something that is recommended).

4. Start SQL Server up normally.

5. Restore MSDB, then Model if necessary.

If you don’t have a valid backup, shame on you! But you can run setup again which will rebuild Master but you have to reattach all databases.

 

Advertisements

Have an opinion? Leave an opinion!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s