Restoring the master Database

A damaged master database makes itself known either with an inability to start SQL Server, by segmentation faults or input/output errors, and/or in a report from DBCC. Damage can be caused by a media failure in the area in which master is stored.

The procedure used to recover a damaged master database is different from the procedure used to recover user databases. If the master database becomes unusable, it must be restored from a previous dump. All changes made to the master database after the last dump are lost when the dump is reloaded and therefore must be reapplied.

It is strongly recommended that the master database be backed up each time it is changed. This is best accomplished by prohibiting the creation of user-defined objects in master and by being aware of the statements and system procedures¾and the equivalent actions in SQL Enterprise Manager¾that modify it.

The most common statements and system procedures that modify master are:

• DISK INIT
• CREATE DATABASE
• ALTER DATABASE
• DISK MIRROR
• DISK UNMIRROR
• DISK REMIRROR
• sp_dropremotelogin
• sp_addumpdevice
• sp_dropdevice
• sp_addlogin
• sp_droplogin
• sp_addserver
• sp_dropserver
• sp_addremotelogin

If a user database is created, expanded, or shrunk after the most recent dump of the master database, and if it becomes necessary to reload the master database, then that user database and all data in will be lost. Because of this, always dump the master database after creating, expanding, or shrinking user databases.

These are the steps required to recover a damaged master database:

    To recover a damaged master database

1. Use the SQL Setup program to rebuild the master database.

   You must rebuild using the same character set and sort order as the master database dump that will be reloaded.

2. Add a dump device (unless the dump will be reloaded from diskettes).
3. Restart SQL Server in single-user mode.
4. Restore the master database from the most recent dump.
5. Apply to the master database any changes that were not included in the most recent dump.
6. Restore the msdb database.

These steps are explained in the sections that follow.

Rebuild the master Database

Use the SQL Setup program to rebuild the master database.

    To rebuild the master database

1. From the Microsoft SQL Server 6.0 program group, double-click the SQL Setup icon.

   Or from the distribution media, from the directory containing the software compatible with your hardware platform's processor architecture, run SETUP.EXE.

2. Respond to the on-screen instructions until the Options dialog box appears.
3. Select the Rebuild Master Database option, and then choose the Continue button.

   A confirmation dialog box appears.

4. Choose Resume.

   The Rebuild Options dialog box appears.

   

5. To specify the character set, choose the Sets button and complete the Select Character Set dialog box that appears.

   If you will be using the default character set (ISO 8859-1), skip this step.

   Important You must use the same character set and sort order that were previously used for this master database.

6. To specify the sort order, choose the Orders button and complete the Select Sort Order dialog box that appears.

   If you will be using the default sort order (dictionary order, case-insensitive), skip this step.

7. In the Rebuild Options dialog box, choose Continue.

   The SQL Server Installation Path dialog box appears.

   

8. If it is not correctly displayed in the SQL Server Installation Path dialog box, enter the location of the existing SQL Server installation, and then choose Continue.

   The Rebuild Master Device dialog box appears.

   

9. If it is not correctly displayed in the Rebuild Master Device dialog box, enter the location and name of the existing MASTER device. Also enter a MASTER device size, and then choose Continue.

   The setup program rebuilds the master database.

10. When rebuilding is complete and the completion dialog box appears, choose the Exit button.

The files MASTER.DA@ and MASTER.AL@ are stored in the \SQL60\INSTALL directory. When rebuilding the master database (or when installing SQL Server), one of these two files is used by the setup program. When the default sort order and character set are selected, MASTER.DA@ is expanded and copied onto the server, replacing MASTER.DAT. When an alternate character set and/or sort order is selected, MASTER.AL@ is expanded, copied onto the server, and several SQL scripts are run.

Although they were part of earlier releases, SQL Server 6.0 does not need (nor does it provide) a bldmastr utility or an INSTMSTR.SQL script.

Add the Dump Device

If the backup of master was made to a disk dump device or a tape dump device, you must add that dump device before performing the load. (This is not necessary if you will be loading from a diskette dump device.) Use SQL Enterprise Manager or the sp_addumpdevice system procedure to add the dump device. The following procedure assumes that you are using SQL Enterprise Manager.

    To add the dump device

1. Start SQL Server normally and start SQL Enterprise Manager.
2. From the Server Manager window select a server, from the Tools menu choose Backup/Restore, and then from the Database Backup/Restore dialog box that appears, choose the New button.

   The Create Dump Device window appears.

   

3. In the Device Name box, type the logical name of the dump device that contains the most recent master database backup. For example:

   Tape0

4. In the Device Path box, type the physical name for that dump device.

   This is the path and filename of the operating-system file that contains the backup. For example:

   \\.\TAPE0

   If you do not remember the path, you can choose the Locate button and select a dump device file from the directory tree that is displayed. For more information about physical names see About Dump Devices, in Chapter 5, "Managing Devices."

5. Select a device type:
   • Disk specifies a dump device file on a hard disk (or other disk media).
   • Tape specifies a dump device for a tape drive.
6. If you selected Tape in step 4, also select or clear the Skip Headers check box.

   This option indicates whether ANSI tape labels are read or ignored.

7. Choose the Add button.

   The dump device is added.

For information about adding a device using the sp_addumpdevice system procedure, see the Microsoft SQL Server Transact-SQL Reference.

Restart SQL Server in Single-user Mode

Before you can reload the master database, you must start SQL Server in single-user mode.

    To start SQL Server in single-user mode

1. If it is already running, stop the server.
2. From a command prompt, type:

   sqlservr /c /dmaster_device /m

   where

   /c

   Starts SQL Server independent of the Windows NT Service Control Manager.

   /dmaster_device_ path

   Specifies a physical name for the MASTER database device. For example:

   /dc:\sql60\data\master.dat
   

   /m

   Specifies single-user mode.

For example:

sqlservr /c /dc:\sql60\data\master.dat /m

Restore the master Database from the Most Recent Backup

Reload the most recent dump of the master database using SQL Enterprise Manager or the LOAD DATABASE statement.

When using SQL Enterprise Manager, in the Server Manager window the server status may appear red since the server was started independent of the Windows NT Service Control Manager. However, you can ignore this status. You will be able to connect and then restore the master database from the Database Backup/Restore window.

You can also restore the master database using the LOAD statement from isql (or another query interface).

    To restore the master database using the LOAD statement

• Type:

  LOAD DATABASE dbname
  FROM dump_device [, dump_device2 [..., dump_device32]]

  where

  dbname

  Is the database name (in this case, master).

  dump_device

  Is the logical name of the dump device.

For example:

LOAD DATABASE master FROM tape0

Important When the load of master is complete, the server automatically shuts itself down. An error message appears, stating that the server connection has been broken. You can then restart the server normally.

Additional options can be used with the LOAD DATABASE statement. For information, see the Microsoft SQL Server Transact-SQL Reference.

Apply Changes to the master Database

If there have been no changes to the master database since the last dump, then you are done. Restart the server normally (in multiuser mode).

If login IDs or devices have been added to or dropped from the master database since the last dump, those changes must be reapplied. Restart the server and reapply the changes manually or from saved batch files.

If databases have been created, expanded, or shrunk since the last dump of master, those databases must be re-created and all the data contained in them must be reentered. This can be done manually, from saved batch files, or by reloading dumps of those databases after they have been re-created.

If you have made many changes and have no recent dump, it is possible that by reloading master in some cases you can regain data in user databases that has been lost. This technique requires the use of DISK REINIT and DISK REFIT and can involve manual modifications to the master database tables.

Use DISK REINIT to re-create rows in sysdevices for all database devices that have been added after the most recent dump. DISK REINIT updates sysdevices just as DISK INIT does, but it does not format the physical disk file, so existing data is preserved.

Use DISK REFIT to re-create rows in sysusages and sysdatabases for all CREATE and ALTER DATABASE statements that were performed after the most recent dump. DISK REFIT scans the physical file associated with each space that is allocated to databases. It also adds the corresponding sysdatabases entries. Some of the information is not reconstructed perfectly. For example, the original virtual device number is not assigned, because it is not known. Instead, virtual device numbers are assigned sequentially. The database owner is not extracted while scanning the physical files; ownership is set to the system administrator. It is also not possible to determine how many sysusages entries originally existed. DISK REFIT inserts a separate entry for each different segment type.

When this is done, correct the entries made by DISK REFIT to sysdatabases and sysusages (if desired) and also add to syslogins any login IDs that were not retained. Then shut down and restart SQL Server.

Caution Capturing the latest changes made to a database by using DISK REFIT and DISK REINIT to re-create the master database is possible, but it is preferable to keep the master database current by dumping it after creating or altering databases. Using DISK REFIT and DISK REINIT is a complicated process that can result in data loss because many of the changes made to a database often must be reconstructed manually in the master database. If you feel this technique is necessary, contact your primary support provider before beginning the recovery process.

After all the changes since the last dump of master have been applied, restart the server normally (in multiuser mode).

Restore the msdb Database

The msdb database supports SQL Executive and provides a storage area for scheduling information. The schedules that you implement using SQL Enterprise Manager are maintained in the msdb database. This includes such things as the tasks that you schedule from the Task Scheduling window, the automatic backups you schedule from the Database Backup/Restore window, and all replication tasks (which are automatically created by the system if the server is configured as a replication distributor).

During installation of a server, the setup program automatically creates two devices (of 2 MB and 1 MB) on the same disk drive as the master database, and then places the msdb database on the 2 MB device (MSDBDATA) and its transaction log on the 1 MB device (MSDBLOG). Scheduling information is then stored in this database.

During a rebuild of the master database, the setup program drops and re-creates the msdb database, which results in a loss of all scheduling information. Therefore, after the master database has been restored, you must perform these steps to restore msdb:

    To restore msdb

1. If necessary, expand the MSDBDATA device, and then allocate the expanded space to the msdb database.

   The msdb database must have as much or more space allocated to it as was allocated to it before the master database was rebuilt.

2. Restore the msdb database from the most recent database backup.
3. Apply all transaction log dumps that were performed after that database dump.
4. Re-create any scheduled tasks that were implemented after the last transaction log dump.

In general, the msdb database can be treated as a user database, as described in Re-creating and Reloading Lost Databases.