Rebuild and Restore Master Database in SQL Server 2000
Recovering From a Situation Where SQL Server 2000 Won't Start Because the Master Database Has Been Lost
Description:
In this exercise, we are going to do assume that the master database has been lost and that SQL Server is unable to start. First we're going to rebuild the master database, then we are going to restore it from backups that we had taken prior to the failure. We're also going to restore other databases on the server along the way to give you an idea of how to plan for such a failure if you have multiple production databases on the server.
(back to top)Requirements:
- As with all of the exercises on this site, do not perform this exercise on a production server. Perform this exercise in a closed development environment
- You will need to have a backup device already created - for the exercise on creating a backup device, click here
- Get backups of the system databases - the restore process will overwrite all of them, so it's a good idea to have a backup
Setup:
None.
(back to top)GUI Steps:
1) Create a new database. In Enterprise Manager, right click on "Databases" and choose "New Database"
2) The "Database Properties" screen pops up. Name your database "newdatabase" and click the "OK" button. You don't even have to look at the Data Files or Transaction Log tabs, we'll just take the defaults.
3) Create four backup devices. One for each of the following databases: model, master, msdb, newdatabase. Remember where you are putting these backup devices on the file system, you will need to know the location later. Below is how we set up our master backup device. If you need more information on setting up a backup device, please see the exercise about Creating Backup Devices.
4) Now back up each of the four databases mentioned in step 1 (master, model, msdb, newdatabase) to their respective backup devices that we created earlier in this exercise. Be very careful not to back them all up to the same device, match each database to its own backup device. If you need help backing up databases, see the Database Backup exercise.
5) We are now going to find all of the files belonging to the master database. Right click on the master database and choose properties.
6) Once on the properties screen, note the location of the data files and transaction log files by looking at the corresponding tabs. We are going to delete these files to simulate a complete loss of the master database.
7) Exit out of Enterprise Manager and Query Analyzer if they are open. Stop SQL Server by right clicking on the SQL Server icon in the systray. You'll be prompted to make sure that this is what you want to do, click the "Yes" button.
8) At this point, you should probably make copies of the files we discovered in step 6 and rename them. That way if the restore doesn't work the first time, you should be able to just put the files back and restart the database. After you have made the copies, delete the master database files that we found in step 6.
9) If you now attempted to start the database by right clicking on the SQL Server icon in the systray and choosing "MSSQLServer-Start", nothing would happen. The master database is damaged, and you need the master database to even start SQL Server.
10) Due to the bug mentioned in the Setup portion of this exercise, we need to do a little manual labor. Find the \x86\data directory on your installation CD and copy all files in this directory to a designated folder on your hard drive. The folder I copied the files into is C:\masterrecover. As you probably already know, when you copy a file from CD to the hard drive, the file will be read only. The files must be made read-write for the rebuild master utility to work. Highlight all of the files and right click. The properties screen will pop up. Uncheck the "Read only" box in the attributes section.
11) We are now going to rebuild the master database and then eventually bring back the backup that we created earlier in this exercise. Go to the directory that contains the rebuildm.exe application and run it. It will be in the Microsoft SQL Server\80\Tools\Binn directory.
12) The Rebuild Master utility appears. Enter your server name and the directory on your hard drive where you copied all of your files to. Then click the "Rebuild" button.
13) You will get notifications about copies occurring and about server configuration. If all goes well, you will eventually get the following prompt:
14) For illustrative purposes, we are going to restart SQL Server. Right click on the SQL Server icon as shown in step 7, only this time select "MSSQLServer - Start". Start Enterprise Manager and browse the databases. Note that the "newdatabase" that we created earlier in this exercise is now gone. All of the system databases shown have been copied directly off of the installation CD (via the directory that we copied these CD files to). "newdatabase" that we created earlier isn't shown because the master database keeps track of all of the user created databases, and the master database is contains only the information from the new installation. Also note that all of the backup devices we created before are gone as well because we are dealing with a brand new master database as well as a new msdb database, which keeps track of backup devices, backups, and jobs among other things. We're going to have to restore master and msdb to get the newdatabase back and to get our backup devices back.
15) Exit Enterprise Manager and query analyzer if they are open. Start up SQL Server in single user mode as shown below. The sqlservr.exe application should be located in the MSSQL\binn directory. Start the database using the -c and -m options. Minimize this DOS window to keep it out of the way, but don't close it.
- -c option shortens startup time
- -m option starts up the database in single user mode. The database must be in single user mode to restore the master database.
For more information, on starting the database in single user mode, look in Books Online under "Startup Options"
16) Start up Enterprise Manager. Right click on the master database, then choose "All Tasks" and "Restore Database".
17) The Restore Database screen pops up. Click the "From Device" radio button and then click on the "Select Devices..." button.
18) The "Choose Restore Devices" screen pops up. We're going to restore from disk, to do this, we need to find our file, click the "Add" button.
19) The "Choose Restore Destination" screen is now shown. We can't choose a backup device because our database in its current form doesn't recognize any of our backup devices, and it won't until we restore the msdb database - which we will do later. For now, click the "File name:" radio button and click on the ellipses to search for our master backup device.
20) Find the master backup device you created very early in the exercise and click the "OK" button.
21) You'll now progress back through the screens we've already been on. Click "OK" on the "Choose Restore Destination" screen.
22) Click "OK" on the "Choose Restore Devices" screen.
23) Once we're back on the "Restore Database" screen, click on the "Options" tab.
24) We're just viewing the files that are going to be restored. Note that these files will overwrite our existing data files, which is exactly what we want. Click the "OK" button.
25) You'll see a restore progress screen. Once the restore is complete, you'll get the following notification - this is not an error message. The database has been successfully restored and now you are no longer connected in single user mode. You'll also receive a message indicating that your connection in Enterprise Manager has been broken. Just close completely out of Enterprise Manager to avoid the annoying error messages.
26) Start up SQL Server by right clicking on the icon and selecting "MSSQLServer - Start"
27) Go into Enterprise Manager and browse the databases. The newdatabase has once again returned. We never wiped out the data files for "newdatabase", but now we have restored our original master database which has all requisite information to load the master database.
28) Your next step will be to restore the msdb database. The steps will be similar to the master database restore (steps 16-24) except you don't need to be in single user mode to restore the msdb database. After you restore the msdb database, restoring other databases on the server becomes much easier because the backup devices have been restored. If you go to restore model or northwind, the suggested restore files are presented directly to you, instead of having to try to find the .BAK file on disk.
(back to top)T-SQL Steps:
There are no T-SQL steps provided for this exercise. The rebuild master utility is graphical. Backups and restores that were performed in this exercise can be done in T-SQL by looking at other exercises on this site.
(back to top)