Lesson 5: Restoring System Databases

[Previous] [Next]

If the media that contain the system databases are damaged, you may have to rebuild the system databases. This lesson describes how to do this, as well as how to restore system databases and attach user databases once the system databases are rebuilt.

If the SQL Server service can be started, you can use the RESTORE DATABASE statement or SQL Server Enterprise Manager to restore the system databases from a valid backup.

After this lesson, you will be able to

• Rebuild the system databases
• Restore damaged system databases
• Attach user databases

Estimated lesson time: 15 minutes

Rebuilding System Databases

If the master database is damaged and you cannot start SQL Server, perform the following steps:

1. Rebuild the system databases with the rebuildm.exe command-prompt utility stored in Mssql7/Binn.
2. Restart the SQL Server service.
3. Restore backups of the system databases, as described next.

CAUTION

---

Rebuilding the system databases overwrites the existing master, model, and msdb databases.

Restoring System Databases

After you have rebuilt the system databases and started SQL Server, you should perform the following steps:

1. Restore the master database from a backup, if one exists.

If a valid backup of the master database does not exist, use SQL Server Enterprise Manager or execute scripts to re-create the information stored in the master database. Create serverwide objects such as logins. It is not necessary to create databases; these can be restored or attached as described in the next section.

2. Restore the msdb database from a backup, if one exists.

You must restore the msdb database when you rebuild the master database. When you run the rebuildm utility, the msdb database is re-created. Therefore, all scheduling information is lost.

3. Restore the model database from a backup, if one exists. You must restore the model database when you rebuild the master database. When you run the rebuildm utility, the model database is re-created. Therefore, any changes to the model database are lost.

Attaching or Restoring User Databases

You either attach or restore user databases, depending on whether the master database was rebuilt.

If the master database was restored from a valid backup, it will contain references to each user database. If the databases are not damaged, no further action is needed. If the databases are damaged, restore them from backups.

If the master database was rebuilt and a valid backup was not applied, you must do one of the following:

• If the database files are not available, restore the user databases from a backup.
• If the database files are available, attach them to the server, using either the sp_attach_db or sp_attach_single_file_db system stored procedure.

Attaching existing database files updates the master database to correctly reference a user database. This is faster and easier than restoring from a backup and can be done without using a database backup. Backups are still necessary if the database files are damaged.

The following example attaches the Northwind database to the master database.

USE master EXEC sp_attach_single_file_db @dbname = 'northwind',  @physname = 'C:\Mssql7\Data\Northwind.mdf' 

Lesson Summary

In this section you learned how to rebuild the system databases in the event they are damaged. The rebuildm.exe utility allows you to rebuild the system databases in case of failure. Once you run this utility, you can then restore the databases from valid backups.