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.
The master database can only be restored from a backup created on an instance of SQL Server 2000. Restore of master database backups which were made on SQL Server version 7.0 or earlier is not supported.
Note Any database users previously associated with logins that need to be re-created are orphaned because the login is lost. For information about associating an existing database user to a new SQL Server login, see sp_addlogin. For information about associating an existing database user with a Microsoft Windows NT® 4.0 or Windows® 2000 user, see sp_grantlogin.
If any user databases were created after master was backed up, those databases cannot be accessed once master is restored unless:
- The databases are restored from backups.
-or-
- The databases are reattached to SQL Server. It is recommended that you attach the databases to avoid restore time.
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 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.
After restoring master, the instance of SQL Server is stopped automatically. If you need to make further repairs and wish to prevent more than a single connection to the server, you should start the server in single user mode again. Otherwise, the server can be restarted normally. If you choose to restart the server in single-user mode, all SQL Server services (except SQL Server itself) and utilities, such as the SQL Server Agent, should be stopped because they may try to access the instance of SQL Server.
When master has been restored and any changes have been reapplied, back up master immediately.
To start the default instance of SQL Server in single-user mode