Restoring the SQL Server MASTER database

Category: How do I?
Date: 06 Aug 2009
Product: SQL Backup
SQL Server requires the server to be in single-user mode before the system's master database can be restored. The SQL Backup console opens more than one connection, which is not allowed in single-user mode.

The master database must be restored using the command-line SQL Backup implementation (sqlbackupc.exe), because it only opens a single connection for the restore operation.

1. Start the SQL Server in single-user mode.
· Open Control Panel->Administrative Tools->Services and stop the SQL Server instance.
· Open a command prompt and change directory to the SQL Server program folder
   For instance, %programfiles%\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
· Start SQL Server in single-user mode by running 'sqlservr.exe -m'
   If this is a named instance, the -s argument must also be used, ie sqlservr.exe -m -sSQLEXPRESS

2. Restore the MASTER database using SQL Backup's command-line utility
· Open a second command prompt and change the directory to the installation directory of the SQL Backup Server components, ie %ProgramFiles%\Red Gate\SQL Backup\(LOCAL)
· If your current Windows logon has SYSADMIN access to the SQL Server, run this command:
   SQLBackupc -SQL "RESTORE DATABASE [master] FROM DISK='<path to master backup>' WITH PASSWORD='<password>'" -I <instance> -E
· If it is necessary to use SQL security, run this command:
   SQLBackupc -SQL "RESTORE DATABASE [master] FROM DISK='<path to master backup>' WITH PASSWORD='<password>'" -I <instance> -U <username> -P <password>
- If you are restoring the master database to a new (or rebuilt) server you will need to add the WITH REPLACE keyword
  SQLBackupc -SQL "RESTORE DATABASE [master] FROM DISK='<path to master backup>' WITH PASSWORD='<password>', REPLACE" -I <instance> -E

3. Start the Microsoft SQL Server Service in normal (multi-user) mode
· Locate the first command prompt, which is running the SQL Server and displaying a log of operations
· Click in this window and press CTRL+C. The SQL Server should indicate it is stopping.
· Go back to Control Panel->Administrative Tools->Services and start the SQL Server Service, and any dependent services.
· In some cases, it may be necessary to restore other system databases (model and msdb), especially if the backup of master was taken from a previous version of SQL Server. The server does not need to be in single-user mode to restore these databases.