rebuild master database in sql server 2016 step by step

RESTORE DATABASE master FROM DISK = 'C:SQLBackupsmaster.bak' WITH REPLACE After I connect to the instance, I can restore the master database using: Next, I will open up a command window (running as administrator) connect to my named instance using: SQLCMD –S. You can read more about the available switches here. The –c switch allows for faster startup time when SQL Server is not being run as a service. sqlservr.exe –mīut since I am using a named instance (JAMBON), I need to include the –s switch followed by the name. If you are using the default instance of SQL Server then you only need to run.

One 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 the login didn’t already exist before we started: SELECT * 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: BACKUP 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.

Rebuild master database in sql server 2016 step by step