If you are using the default instance of SQL Server, then you only need to run:īut since I am using a named instance (JAMBON), I need to include the –s switch followed by the name. Once there, I will run the following command: I will navigate to the directory where the sqlservr.exe resides (for me that is C:Program FilesMicrosoft SQL ServerMSSQL11.JAMBONMSSQLBinn, probably not the same for you). Now we need to restart the instance using the –m startup parameter from a command window. I’ll just use the default PowerShell command window:

Here’s a simple enough script to do that, complete with a check that the login didn’t already exist before we started: OK, now we will create that dummy login as part of our practice run. That way when the restore was complete, they could verify that the login disappeared.įirst up, let’s take a backup of the master database:īACKUP DATABASE TO DISK = N'C:SQLBackupsmaster.bak' WITH INIT As part of the test, I would have them create a SQL login at the beginning. I would have my team practice this every now and then on a test server just to make sure their skills were sharp should they ever need to restore master when half-asleep in the middle of the night.

Restore master from file WITH REPLACE (Note: Windows will close, do not panic!).

You are running backups, right? Good.Īssuming you have a good backup of master to use, the process I use is as follows: Of course, I am assuming you have a backup.

Restoring the master database is surprisingly easy. But when the times comes (in the middle of the night, of course) you will want to be prepared to do the following steps. In other words, this isn’t something you will find yourself needing to do very often. In my seven years as a production DBA, I can count on one hand the number of times I have need to restore the master database as a result of a disaster.

Backup sql master