Considerations for Restoring the master Database

  • Article

If changes have been made to master since a backup was created, those changes are lost when the backup is restored. You must re-create those changes by executing the statements that re-create the missing changes. For example, if any SQL Server logins have been created since the backup was performed, the logins are lost when master is restored. Re-create the logins by using SQL Server Management Studio or by using the original scripts with which the logins were created.

You can restore the master database only from a backup that is created on an instance of SQL Server 2005.

Note

Any database users that were previously associated with lost logins are orphaned, that is they cannot access the database. For more information, see Troubleshooting Orphaned Users.

After you restore master, the instance of SQL Server is stopped automatically. If you have to make additional repairs and want to prevent more than a single connection to the server, restart the server in single-user mode. Otherwise, the server can be restarted regularly. If you decide to restart the server in single-user mode, first stop all SQL Server services, except the server instance itself, and stop all SQL Server utilities, such as SQL Server Agent. By stopping the services and utilities, you prevent them from trying to access the server instance.

Reconstructing Changes That Are Made After the Backup Was Created

If a user database was created after the restored backup of master, that user database is inaccessible until one of the following occurs:

  • The database is attached. We recommend this method.
    Attaching a database requires that all of the database files are available and usable. We recommend specifying the log files, and also the data files, instead of having the attach operation try to rebuild the log file or files.
    For information about how to attach a database, see How to: Attach a Database (SQL Server Management Studio) or CREATE DATABASE (Transact-SQL).
  • The database is restored from one or more backups.
    Restore the database only if its data files or transaction log files no longer exist or are unusable.

Attaching or restoring a database, re-creates the necessary system table entries, and the database becomes available in the same state as before the master database was restored.

If any objects, logins, or databases, have been deleted after master is backed up, you must delete those objects, logins, and databases 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 can no longer find those databases. Those databases should be dropped after the backup is restored.

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

To start an instance of SQL Server in single-user mode

How to: Start an Instance of SQL Server (sqlservr.exe).

To restore the master database

To attach a database

See Also

Concepts

Considerations for Backing Up the master Database
Detaching and Attaching Databases
Starting SQL Server in Single-User Mode
Considerations for Rebuilding the master Database

Other Resources

System Tables (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006
Changed content:
  • Added recommendation to specify the log files, and also the data files.