SQL Server: Recovering the Master Database
- Rebuilding and Recovering the <tt>master</tt> Database
- Potential Problems in Rebuilding and Restoring the <tt>master</tt> Database
- Recovering Other Databases after <tt>master </tt> Has Been Rebuilt
- Notes on Logins and Users after a <tt>master</tt> Rebuild
- Program to Generate Logins from the User Database
- Remapping Orphaned Users
- Recovering msdb
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
Introduction
Chapter Objectives
Rebuilding and Recovering the master Database
Potential Problems in Rebuilding and Restoring the master Database
Recovering Other Databases after master Has Been Rebuilt
Notes on Logins and Users after a master Rebuild
Program to Generate Logins from the User Database
Remapping Orphaned Users
Recovering msdb
The master database has specific system information common to all databases (server logins, for example). If you lose your master database, chances are you are not going to start your SQL Server.
This chapter goes over the process of rebuilding your master database, then recovering it from a backup. Additionally, we go over some other critical areas that often get overlooked in a recovery, namely logins and user accounts getting out of synchronization. We'll go over two tools on how to fix common problems.
We'll also cover some other common problems that you may encounter when rebuilding the master database and how to work around them.
Rebuilding and Recovering the master Database
The master database is special. When the master database becomes damaged in any way, chances are your SQL Server is not going to work. In this section, we'll rebuild the master database and then restore it. Remember that once you rebuild master, you lose all information and data. So, make sure your databases are backed up. If your database files still exist and are recoverable, you can re-attach the databases by using the SQL EM or by using the stored procedure sp_attach_db. Let's run through a typical recovery situation.
Scenario
Our system took a power hit and we lost a disk drive. When we try to start SQL Server backup, we discover that it won't start, so we look at the error log. The default location for the errorlog is in the (. . .\LOG) directory:
2001-04-28 14:53:18.45 serverMicrosoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: )
2001-04-28 14:53:18.50 server Copyright (C) 1988-2000 Microsoft Corporation.
2001-04-28 14:53:18.50 server All rights reserved.
2001-04-28 14:53:18.50 server Server Process ID is 1364.
2001-04-28 14:53:18.50 server Logging SQL Server messages in file
'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2001-04-28 14:53:18.53 server initconfig: Error 2(The system cannot
find the file specified.) opening 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\master. mdf' for configuration information.
We've lost the master database data file. Use the rebuildm tool located in the . . .\tools\binn directory (C:. . .\tools\binn> rebuildm.exe) to rebuild the master database.
Running the rebuildm tool displays the screens shown in Figures 4–1 through 4–5. You will be asked for information such as the location of the data files (the original data files that came on the CD), which are located in the . . .\x86\data directory on the CD. I strongly suggest you copy the files to a local hard disk before you attempt to run the rebuild. It takes some of the headaches out of the restore, although it's not mandatory.
Figure 4–1 shows the splash screen for the Rebuild Master tool. Enter the server name and the location of the data files master.mdf and master.ldf.
)
Figure 4–1 Rebuild Master initial screen.
Once you provide the location, and if your sort order is different from the default, change the sort orders for the databases. This information will be in the sp_helpdb and sp_helpsort that you saved before. You can also see the sort information from previous backups by looking at the header information. The backup stores this information on the backup device. In this example, we will take the defaults. Next, SQL Server will confirm that we want to rebuild the master and overwrite the system information stored in the system database as shown in Figure 4–2. Select Yes.
)
Figure 4–2 Rebuild Master confirmation screen.
SQL Server will next start copying the data files from the source to the target locations. Figure 4–3 shows what you will see.
)
Figure 4–3 Rebuild Master copying files.
Following the files being copied over, SQL Server will configure itself so that the new master is recognized as seen in Figure 4–4.
)
Figure 4–4 Rebuild Master configuration.
When these steps are completed, the exit dialog box appears as shown in Figure 4–5.
)
Figure 4–5 Rebuild Master complete.
At this point, we have a clean SQL Server, but one that does not know other databases exist.
Now it's time to restore the backup of master from our last good backup. This will contain the locations of other databases on the server, SQL Server logins, etc.
Restoring the master Database
To restore the master database, you will need to execute the following steps:
Add the backup device:
sp_addumpdevice 'disk', 'master_backup', 'c:\tmp\sql_backup\master_backup.dmp' go
Stop SQL Server.
Bring SQL Server up into single-user mode via the command line. To do this, get to a DOS prompt, change directory into the . . .\MSSQL\BINN directory, and execute the following command:
C:\Program Files\Microsoft SQL Server\MSSQL\Binn> sqlservr.exe -c -m
This will start SQL Server and the output will be directed to the console. Here's a sample of what will be displayed on your console screen (it is also in the errorlog):
2001-04-29 15:44:08.43 spid3 SQL Server started in single user mode. Updates allowed to system catalogs. 2001-04-29 15:44:08.46 spid3 Starting up database 'master'.
Log into SQL Server as SQL Server user sa, or as a trusted administrator.
Restore the master database just as you would any other database. Note that master's recovery mode is simple, so you will not restore any transaction logs.
restore database master from master_backup go
Here's the output from that command:
The master database has been successfully restored. Shutting down SQL Server. SQL Server is terminating this process.
Restart SQL Server. Once master has been restored, SQL Server will automatically shut down. Bring it up just as you normally would.
Restore other user databases as needed. If your other database files are okay, then you will not need to restore them. If other databases were damaged, or if you are rebuilding the server, you will need to restore them.
Resynchronize SQL Server logins and users as needed. If everything is a clean restore, then there are no orphaned users and logins. If there are orphans, then see the sections on creating logins/users and resynchronizing them.