Restoring SQL 2005 Master Using LiteSpeed

Less than 24 hours after posting a list of things I am thankful for, where I expressed Thanks for not having to restore master, I found myself awake at 3AM in order to restore master after we had a server lose its data drive. Before I got started I decided to check on the definition of irony. Yep, it fit.

OK, show of hands: How many people out there use LiteSpeed for your database backups? Now, for those with their hands raised, put your hands down before someone thinks you are crazy for raising your hand while sitting at your computer. Next question: are you also using LiteSpeed to back up your system databases? Final question: do you know how to restore master from a LiteSpeed backup?

No?

Well, how hard can it be? And I am not talking about rebuilding master, but doing a restore of master. So, you startup the instance in single user mode (using -m, which stands for maintenance I believe), and do the restore. What could be easier? Well, lots of things, actually. Turns out that unless you have your process rehearsed you could be fumbling for answers at 3AM. Not good times.

I decided to put together the details on how to get the restore done. Since we rely heavily on support from people on the other side of the Earth (that I have never met) it is vital that I can document as much as possible. And I decided to share the details with all of you as well. You’re welcome.

Since only the data drive was affected in this event all I needed to do was get SQL up and running and start doing restores of the system databases, starting with master. A trick I learned a while back is to copy the master, msdb, and model .mdf and .ldf files from another server to the server we need to recover. We have gone through great pains here to make certain we locate these .mdf and .ldf files in the same path for every server. So, when we start MSSQL, the instance comes alive except for the user databases which are all marked suspect.

But with the instance alive you are now able to get started on recovering master, the important first step. The general outline for this process is as follows:

1. Shut down the instance completely, by whatever means you prefer.
2. Open a command prompt and navigate to the directory that contains the sqlservr.exe executable. This is most likely the \Binn directory and you could run the following: cd \Program Files\Microsoft SQL Server\MSSQL.MSSQLSERVER\MSSQL\Binn
3. Next, you will run the following command: sqlservr.exe -m, which will start the instance. Leave this command window open.
4. Open a second command window and navigate to directory that contains the SQLLitespeed.exe executable. Most likely this is found by running: cd \Program Files\Quest Software\LiteSpeed\SQL Server\Engine (NOTE: do not confuse the SQLLitespeed.exe executable with the Litespeed.exe executable, as I did several times)
5. Next, run the following command, substituting in the file path you want to use for the restore: sqllitespeed.exe -Rdatabase -T -Dmaster -F “[backup file to restore from]”
6. Once complete you will be returned to a prompt in the current command window and the original command window should also have a prompt as the instance will have been shut down.
7. You can now start the instance as a service as you would do normally.

If you are looking to test that this works then I suggest you create a new login right before you shut down the instance in step one. You can create one real quick by running:

USE [master]
 GO
 CREATE LOGIN [master_restore_test]
 WITH PASSWORD=N'test',
 DEFAULT_DATABASE=[master],
 CHECK_EXPIRATION=OFF,
 CHECK_POLICY=OFF
 GO

This will create a login, which you can verify quickly, then you can do the restore of master and verify that the login has disappeared.

I put together these details to share with my team and asked that they all took five minutes to practice restoring master form a Litespeed backup. I hope that none of us will ever need to use these instructions, but if the time comes that such actions are needed I feel better knowing that we have rehearsed these steps.

Believe me, it is better than trying to practice when you are half asleep. And knowing you can recover quickly when disaster strikes gives you confidence to take action when an event happens.