How to: Restore the master Database (Transact-SQL)
This topic explains how to restore the master database from a full database backup.
Start the server instance in single-user mode.
For information about how to specify the single-user startup parameter (-m), see How to: Configure Server Startup Options (SQL Server Configuration Manager).
To restore a full database backup of master, use the following RESTORE DATABASE Transact-SQL statement:
RESTORE DATABASE master FROM <backup_device> WITH REPLACE;
The REPLACE option restores the specified database even when a database with the same name exists. The existing database, if any, is deleted. In single-user mode, we recommend that you enter the RESTORE DATABASE statement in the sqlcmd utility. For more information, see Using the sqlcmd Utility.
Important: After master is restored, the instance of SQL Server shuts down and terminates the sqlcmd process. Before you restart the server instance, remove the single-user startup parameter. For more information, see How to: Configure Server Startup Options (SQL Server Configuration Manager).
Restart the server instance and continue other recovery steps such as restoring other databases, attaching databases, correcting user mismatches, and so on.
The following example restores the
master database on the default server instance. The example assumes that the server instance is already running in single-user mode. The example starts
sqlcmd and executes a
RESTORE DATABASE statement that restores a full database backup of
master from a disk device:
|For a named instance, the sqlcmd command must specify the -S<ComputerName>\<InstanceName> option.|
C:\> sqlcmd 1> RESTORE DATABASE master FROM DISK = 'Z:\SQLServerBackups\master.bak' WITH REPLACE; 2> GO
ConceptsTroubleshooting Orphaned Users
Detaching and Attaching Databases
Considerations for Rebuilding the master Database
Using the SQL Server Service Startup Options
Considerations for Backing Up the master Database
Considerations for Restoring the master Database
Starting SQL Server in Single-User Mode
Other ResourcesSQL Server Configuration Manager
SQL Server Management Studio Tutorial