Tuesday, July 14, 2009

Restoring master DB from a backup in sql server 2005

We recently had some issues with the RAID getting corrupted on one of our production SQL Servers after some repeated power outages caused by our giant UPS failing. As a result we needed to rebuild the RAID from scratch and restore from a full drive backup from Net Backup. Unfortunately Net Backup didn't get all the files on the data drive, most importantly (at least for the purpose of this article!), master.mdf was missing. Fortunately we had a backup of master, but SQL Server won't start without master.mdf, and master.bak doesn't really give you that.
Yes, I realize there are other articles out there about this, but I didn't find any of them to be exactly what I needed, so someone else might find use in mine.
1. So, first step is to rebuild the master DB to a default version, so we can get SQL Server started and restore the master.bak file we made:
a. Run the following command: "M:\ENGLISH\SQL2005\DEVELOPER\SQL Server x64\Servers\setup.exe" /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=
i. Other articles online will tell you to use "start /wait" at the beginning of that command, but I had 0 success with that
ii. M:\ENGLISH\SQL2005\DEVELOPER\SQL Server x64\Servers\ is the path for installing a 64bit Developer edition from the MSDN CD. Yours may differ, but it's CRUCIAL that it be the same as the original path. To find the original path:
1. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\
2. Then look through all of the InstallProperties\DisplayName until you find Microsoft SQL Server 2005 (I started at the above location in the registry then searched till I found the right one)
a. Make sure it's the actual server instance, not one of the other various components
3. Then use the value in the InstallSource key
b. Once you've gotten the above command to run successfully, you'll be getting a fresh copy of your system DBs in the original data location. If you want to monitor setup.exe while it performs this activity use your Task Manager.
c. Once this has completed, confirm that SQL Server works by starting it from the SQL Server Configuration Manager
2. Now we need to restore Master from our backup:
a. Start the SQL Server in single-user mode
i. From a DOS prompt: \sqlservr.exe -m
ii. -m is the switch for Single User Mode.
iii. On the server I'm restoring = E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
iv. It won't appear like the process is complete in the normal sense when the SQL Server is running, if you're like me and are used to seeing the command prompt again after you run something from the command line, but once the messages stop scrolling in the DOS window the SQL Server instance should be started.
v. Confirm the above by looking for sqlserver.exe in Task Manager
b. Restore the backup
i. Personally I scripted this all via Management Studio after I rebuilt the "vanilla" master.mdf, then restarted the SQL Server instance in single-user mode. That way I also had the query window open so I could just run the statement twice (first time it reestablishes connection to the SQL Server) and run it
ii. Here's the command for our install:
RESTORE DATABASE [master] FROM DISK = N'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\master\master_backup_200907082100.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO
iii. Once this has executed, it will stop the SQL Server instance, giving the following messages:
11 percent processed.
20 percent processed.
31 percent processed.
40 percent processed.
51 percent processed.
60 percent processed.
70 percent processed.
81 percent processed.
90 percent processed.
100 percent processed.
Processed 432 pages for database 'master', file 'master' on file 1.
Processed 2 pages for database 'master', file 'mastlog' on file 1.
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.
c. Start the SQL Server service normally via the SQL Server Configuration Manager
i. If you see an error like this:
1. Server local connection provider failed to listen on [ \\.\pipe\SQLLocal\MSSQLSERVER ]. Error: 0x5
2. Try rebooting the SQL Server as it may not have gracefully released control of the ports SQL Server needs the last time it stopped.
d. Restore any other system databases first
i. Most likely you'll need msdb, possibly model
e. Restore any other DBs you may need
f. Ensure that you don't need to reinstall any service packs and patches