Restoring the master Database from a Current Backup

If there have been any changes to master after the database backup was created, those changes are lost when the backup is restored. Therefore, it is necessary to re-create those changes manually after restoring master from a backup by executing the statements necessary to re-create the missing changes. For example, if any Microsoft® SQL Server™ logins have been created after the backup was performed, those are lost when master is restored. Re-create the logins using SQL Server Enterprise Manager or the original scripts used to create the logins.

Note Any database users previously associated with logins that need to be re-created are orphaned because the login is lost. To associate an existing database user to a new SQL Server login, see sp_addlogin. To associate an existing database user with a Windows NT user, see sp_grantlogin.

If any user databases have been created after master was backed up, those databases cannot be accessed unless:

• The databases are reattached to SQL Server.

Or

• The databases are restored from backups.

Attaching the database to SQL Server re-creates the system table entries needed and makes the database available in the same state it was before the master database was restored. It is not necessary to re-create the database first; the files can be attached without knowing how the database was created, as long as all the files comprising the database are attached.

It is necessary to restore a backup of the database only if the data and transaction log files of the database no longer exist or are unusable or damaged in some other way due to a media failure.

If any databases are restored or attached after master is rebuilt, it is necessary to specify the same sort order, code page, and Unicode collation when rebuilding master as used by those databases. If the same sort order, code page, and Unicode collation are not used, it will not be possible to restore or attach those databases.

If any objects, logins, or databases, for example, have been deleted after master was backed up, those objects, logins, and databases should be deleted from master.

Important If any databases no longer exist, but are referenced in a backup of master that is restored, SQL Server may report errors when it starts because it cannot find those databases any longer. Those databases should be dropped after the backup is restored.

When restoring master, SQL Server needs to be started in single-user mode to prevent any additional users from using SQL Server. All SQL Server services (except SQL Server itself) and utilities, such as the SQL Server Agent, can be stopped because they may try to access SQL Server.

When master has been restored and any changes have been reapplied, back up master immediately.

To start SQL Server in single-user mode

To restore the master database

To attach a database

See Also