ID: _to_restore_master_database._Shutting_down_SQL_Server_5_Ruleįailed to restore master database.Once it is started I will open up SSMS and run the following query: SELECT *Īnd then, verify that the login is no longer there.Ĭongratulations! You have just restored your master database, and now you can go about with whatever else you need to get done to recover from whatever disaster struck.Failed to restore master database. I will use SQL Server Configuration Manager to re-start the instance. The Powershell window will reflect that the instance has been shut down: Note that once the restore is complete the instance will be shut down. 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.
0 kommentar(er)
