How do You Restore the Master Database in SQL Server?

Master database stores the information SQL Server needs to start. When it fails the entire instance stops and you need a clear order of steps.

This guide follows the Microsoft restore procedure and flags the checks that prevent a second failure. Commands are untested samples.

What should You Check Before a Restore?

• Full backup. Microsoft states that you restore master from a full database backup.
• Matching instance. Match the version, edition and patch level of the original on the recovery instance. Otherwise it can behave unpredictably.
• Version of the backup. System databases restore only from backups created on the version the instance uses.
• No log backups. Microsoft states that master does not support transaction log backups.

See Microsoft guidance on backup and restore of system databases. If you have no backup see SQL Server disaster recovery options for planning.

What are the Steps to Restore Master?

Microsoft documents this procedure for an instance that can still start. See restoring the master database.

1. Open a command prompt and move to the Binn folder of your instance.
2. Start the instance with the single user startup parameters in the sample below.
3. Open a second command prompt and connect with sqlcmd.
4. Restore the backup with WITH REPLACE.
5. Expect the instance to shut down when the restore completes.
6. Remove the single user startup parameter in SQL Server Configuration Manager.
7. Restart the instance as a service with no startup parameters.

:: Start the default instance in single user mode
:: Replace the path with your own Binn folder
cd "C:\SQL\MSSQLXX\Binn"
sqlservr -c -f -s MSSQLSERVER -mSQLCMD

:: In a second window connect to the instance
sqlcmd -S . -E -d master

-- Replace the path with your own backup file
RESTORE DATABASE master FROM DISK = 'D:\Backups\payroll_master_full.bak' WITH REPLACE;
GO

Microsoft states that WITH REPLACE restores the database even if one with the same name exists and deletes the existing one. For a named instance add the server and instance name to the sqlcmd command.

What do You Do After the Restore?

Microsoft lists three follow up tasks. Restore your other databases, attach databases that are missing and fix orphaned users.

When do You Rebuild the System Databases Instead?

Microsoft states that you rebuild master when severe damage prevents the instance from starting. Rebuilds replace all system databases and you restore your backups of model and msdb afterward. Follow the Microsoft article on rebuilding system databases for the exact Setup command.

Backup master on a schedule and after substantial updates to avoid this problem. Microsoft also lists model and msdb as databases that need a backup. For corruption in user databases see how to resolve SQL Server page level corruption.