Since the login and database were created after the backup they won’t be there after the restore is complete. Just for fun, we will create a new database as well: CREATE DATABASE Next we will create a test login as part of our practice run: USE I’m doing this as part of an exercise here, you will want to make certain that if you take a backup of master on a server in your environment that you aren’t interfering with any existing backup plan: BACKUP DATABASE TO DISK = N'C:\SQL\Backups\master.bak' Restore the Master Database in SQL Server 2012įirst up, let’s take a backup of the master database. That way when the restore was complete they could verify that the login disappeared. As part of the test I would have them create a SQL login at the beginning. I would have my team practice this on a test server a few times a year just to make sure their skills were sharp should they ever need to restore master when half-asleep in the middle of the night. Using SQL configuration manager, restart instance.
Restore master from within the SQLCMD window.In a second command window, open SQLCMD.In the command window, start the instance executable in maintenance mode.Using SQL Configuration manager, stop the SQL Server instance.Here’s a link to the scripts that SQL experts use the world over, made by SQL Server MVP Ola Hallengren.Īssuming you have a good backup of master you can test the restore process by following these simple steps: If you aren’t running backups then you need to get started. Of course I am assuming you have a backup. Restoring the master database is surprisingly easy. When the time comes you will want to be able to restore the master database quickly. So, yeah, it’s kinda a big deal as databases go. If the master database is not available (say, due to corruption or a disaster that wipes away the disk where the master database is kept) then your instance of SQL Server cannot function. The master database stores metadata about the instance of SQL Server: things like server logins, databases and database file locations, server configuration details, and linked server definitions are all stored inside of the master database. But when the time comes (in the middle of the night, of course) you will want to be prepared. With over 180 instances of SQL Server in our care I believe that is a large enough sample size making it safe to assume that restoring master is not something you will find yourself needing to do often. In my seven years as a production DBA I can count on one hand the number of times I have needed to restore the master database as a result of a disaster.
0 kommentar(er)
