Rehearsing a master Database Restore on a Test Instance

A master database restore is too rare and disruptive to learn during an outage. Rehearsing a master database restore on a test instance exposes service names, file paths, single-user connection races, and the automatic shutdown that follows a successful restore. Time every stage and keep the result in the runbook.

Build a Safe Target for a master Database Restore

Use an isolated Windows SQL Server test instance with a matching major version and compatible configuration. Do not restore a production master backup onto an arbitrary shared lab instance; master carries server-level metadata such as logins and configuration. For a procedural drill, back up the test instance's own master and restore it to that same test instance. Preserve a way to rebuild or reinstall the lab if the exercise fails.

I record the instance version, edition, service name, backup location, and startup parameters before touching services. Which account can restart the Engine and connect locally if the normal SQL logins disappear? The rehearsal should answer that question without relying on the instance being healthy.

Back Up master and Verify the File

Take a full backup of master on the test instance. Store it on a path readable by the SQL Server service account and outside the master data directory. Check the file exists, record its size and finish time, and run RESTORE VERIFYONLY as a preliminary check. VERIFYONLY is not a substitute for the actual restore exercise.

BACKUP DATABASE master
TO DISK = 'D:\RestoreLab\master_test.bak'
WITH INIT, CHECKSUM, STATS = 5;
GO
RESTORE VERIFYONLY
FROM DISK = 'D:\RestoreLab\master_test.bak'
WITH CHECKSUM;

Keep a copy of relevant server-level settings before restoring. A master backup represents one point in time; login and configuration changes made afterward are not automatically present after the restore. For a real incident, compare the backup's age with those changes.

Start in Single-User Mode

Stop SQL Server Agent so it cannot take the only connection. Start the Database Engine with -mSQLCMD, which reserves the single-user slot for the sqlcmd client application name. For the default instance, the service name is MSSQLSERVER. A named instance uses its own service name. Run the following only on the isolated test instance from an elevated PowerShell window.

# PowerShell
Stop-Service -Name 'SQLSERVERAGENT'
Stop-Service -Name 'MSSQLSERVER'
& net.exe start MSSQLSERVER /mSQLCMD

Other monitoring or application services should be disconnected from the test instance. Confirm the Engine started with the expected parameter in the error log. If another sqlcmd process holds the slot, close it before the restore connection. Do not leave a normal SSMS Object Explorer window open and assume it is harmless.

Run the master Database Restore Through sqlcmd

Use Windows authentication as a local administrator or an approved sysadmin login. Connect with sqlcmd and run the RESTORE from the test backup. WITH REPLACE is necessary for this explicit master replacement; use it only on the isolated target whose current master you intend to replace. The command is shown as T-SQL for a sqlcmd input file.

RESTORE DATABASE master
FROM DISK = 'D:\RestoreLab\master_test.bak'
WITH REPLACE, STATS = 5;
GO

Save that text as restore-master.sql, then run sqlcmd -E -S localhost -d master -i D:\RestoreLab\restore-master.sql from an elevated Windows command prompt or PowerShell. For a named instance, use localhost\InstanceName. Capture the output. Do not chain additional validation queries after RESTORE because SQL Server shuts down when master restore completes.

Expect the Shutdown After a master Database Restore

A successful master restore terminates the sqlcmd connection and shuts down the instance. That is expected, not proof of failure. Check the RESTORE messages and service state. Remove the temporary single-user startup parameter before restarting normally. When -mSQLCMD was supplied only on the net start command, a plain service start omits it; if it was added in Configuration Manager, remove it there.

# PowerShell
Start-Service -Name 'MSSQLSERVER'
Start-Service -Name 'SQLSERVERAGENT'

Wait for database recovery and inspect the Engine error log. The service can start while user databases still recover. Record elapsed time from stopping Agent to the first successful administrative connection, and from there to ready application checks.

Check Server Metadata and Workload

After normal startup, query sys.server_principals for logins, sys.servers for linked servers, and sys.configurations for important settings. Compare with the pre-rehearsal inventory. Check SQL Server Agent jobs in msdb separately; restoring master does not restore an older msdb. Test a representative user database connection and a linked-server dependency if one exists.

SELECT name, type_desc, is_disabled
FROM sys.server_principals
WHERE type IN ('S','U','G');
SELECT name, product, data_source FROM sys.servers;
SELECT name, value_in_use FROM sys.configurations
WHERE name IN (N'max server memory (MB)',
               N'max degree of parallelism');

Some metadata changes after the backup time will be absent. That is the point of comparing inventories. I record the repair steps, missing objects, and timing in the runbook. A rehearsal that only sees the service start but never checks logins and configuration has tested the command, not the recovery.

Inventory What master Carries

Before the rehearsal, export a read-only inventory of logins, server roles, credentials, endpoints, linked servers, and server-level configuration. Some related objects live in msdb or other system databases, so restoring master alone is not full instance recovery. Note the service account and encryption dependencies separately. The objective is to identify what an older master backup will restore and what later changes need to be recreated.

Rehearse the Wrong-Backup Branch

Check version compatibility and backup provenance before restoring. A master backup from another instance can carry wrong paths and metadata, and a backup from an incompatible build is not a safe substitute. In the lab, deliberately verify the label and backup header against the expected instance before the destructive step. Store a second recovery route: if the test instance will not start after the drill, rebuild it from approved installation media and documented settings.

A successful RESTORE VERIFYONLY only checks parts of backup readability. The real test is the complete restore, normal restart, and metadata comparison. I time the slowest recovery step, not just the RESTORE statement. That is the number the on-call team needs when estimating an outage.

Capture the Runbook Details

Write down every service command, sqlcmd command, path, credential source, and observed message. Include the expected automatic shutdown prominently. A responder who sees the service stop after RESTORE can otherwise misread success as another failure and waste time restarting in single-user mode. Keep screenshots or text of the validation queries and the final service state.

After the drill, remove temporary backup files from the test host according to retention policy and protect the durable backup in the approved store. Schedule the next rehearsal after a major SQL Server upgrade or change to the server-level security model. A runbook that worked on a previous build is useful evidence, but it needs a current test.

Related reading on this blog: Master Database in Single User Mode: A Serious Issue and How to Migrate Master Database to New Location?.

A master backup is not a recovery plan, it is one input to a restore you have practiced.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.