For it's part, SQL Server is extremely reliable. However, no matter how many precautions we take, software is always prone to failure. Things that can go wrong will at some point go wrong. SQL Server is no exception. Probably the worst event that can happen to SQL Server is having SQL Server not start up. If you're able to get SQL Server to start up you have a chance at survival. If you can't, you need to perform some tricks to reconstruct a master file. For those that don't know, the master database is the heart of SQL Server. Let's get one thing out in the open. "DO NOT MESS WITH THE DATA DIRECTLY IN THE MASTER DATABASE". Got it. Bad things. Very bad things can happen.
Today we're going to explore how to bring SQL Server backup in the event something has happened to SQL Server. I had the system administrators where I work create for me a VM. I then went on to install the eval version of SQL Server 2008R2 on the box. First thing we need to do is to create a database so that we can demonstrate that we really did restore the master database. Open up SQL Server Management Studio and type the following:
Create database myTestDatabase
go
Next thing we want to do is to backup all of our system databases. This includes master, model, and msdb. No need to backup tempdb as it get reconstructed everytime SQL Server gets restarted. The code to backup the system databases is as follows:
backup database master
to disk = 'c:\master.bak'
backup database model
to disk = 'c:\model.bak'
backup database msdb
to disk = 'c:\msdb.bak'
Now we need to start breaking stuff. Let's first stop SQL Server by opening up a command window and typing:
net stop "MSSQLSERVER"
This assumes that your instance is the default instance. If you have a named instance, use the following:
net stop "MSSQL$<<instancename>>"
Now that our database is stopped we can remove (or rename) the master.mdf and mast.log files. In case you think that what we just did doesn't really hurt anything. Let's try to restart SQL Server. The code is similar to stopping the service.
net start "MSSQLSERVER"
or
net start "MSSQL$<<instancename>>"
You'll notice that it errors out. A quick look at the error log.
Now what? In order to get SQL Server to a point where we can start it and log into it we'll need the install media. You still have that right? We'll use some special switches and parameters to get the install media to build us some system database files and log files.
C:\Documents and Settings\Administrator>"c:\SQLServer\setup.exe" /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=BUILTIN\Administrators /SAPWD=mySuperSecurePassword
Because we're using setup.exe, we don't really want to install the entire SQL Server application again so we'll use some parameters. These are taken from Microsoft's website.
/ACTION=REBUILDDATABASE
As the name describes, this specifies that Setup re-create the system databases.
/INSTANCENAME=MSSQLSERVER
This is the name of the instance of SQL Server. For the default instance, enter MSSQLSERVER.
/SQLSYSADMINACCOUNTS=BUILTIN\Administrators
Specifies the Windows groups or individual accounts to add to the sysadmin fixed server role. When specifying more than one account, separate the accounts with a blank space. For example, enter BUILTIN\Administrators MyDomain\MyUser. When you are specifying an account that contains a blank space within the account name, enclose the account in double quotation marks. For example, enter NT AUTHORITY\SYSTEM.
/SAPWD=mySuperSecurePassword
Specifies the password for the SQL Server sa account. This parameter is required if the instance uses Mixed Authentication (SQL Server and Windows Authentication) mode.
You'll notice if you look at the directory where the database files are kept we now have files for master, model, and msdb. In my case that's E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA.
Let's take a stab as getting sql server to start up. Like before we'll use the net startup command.
net start "MSSQLSERVER"
or if you have an instance:
net start "MSSQL$<<instancename>>"
At this point you should be able to log in. Let's check to see if our database is available by using the following code:
select * from sys.databases
Uh... Oh! What happened to our newly created database? Because we had to rebuild the master database and log files, SQL Server doesn't know that our database even exists. But as I mentioned earlier, if we can log on to SQL Server we can restore our backups. You did backup your databases right? We now need to overwrite the master database with the database in our backup. We'll need to stop the SQL Server service. I've showed you how to do that above but we'll need to add the '/m' switch to get the SQL Server service to start in single-user mode.
net start "MSSQLSERVER" /m
or if you use a named instance:
net start "MSSQL$<<instancename>>" /m
while you have that command line window open we need to use SQLCMD.exe to allow us command line our restore operation. You'll find SQLCMD.exe in the directory C:\Program Files\Microsoft SQL Server\100\Tools\Binn\. In the command shell window type:
sqlcmd.exe
You'll notice that your prompt has now changed to a line number and a '>'. This means you're now in SQL Server.
We use 'with replace' to overwrite the current master database. Once it gets restored, SQL Server will kick you out to a DOS prompt. Now that we've restored the master database, we can start up the service like I demostrated above. We'll want to next query sys.databases to make sure that SQL Server know where our database is now.
select * from sys.databases
You'll notice that our database listed.
At this point, we can restore the model database if you use model as a template to save your company specific settings for creating databases. If you had SQL Agent jobs, you'll also want to restore the msdb database. Unlike before, it is not necessary to use SQLCMD.exe to restore these database. They can be done from within SQL Server Management Studio.
Congratulations! You've recovered from a catastrophic SQL Server failure. I hope it also demostrates why backups (especially of system databases) are extremely important. Take the time to practice this exercise. Hopefully it's not something you'll have to use but when you do you'll be prepared.
Thanks
J.D.