Restoring the master database in SQL Server is a critical recovery procedure used when the system databases are corrupted, when you need to move a server, or after a failed upgrade. This guide walks you through the exact steps to restore master from a full backup, including the necessary single-user mode startup and post-restore verification. You will need administrative access to the Windows server and a valid master database backup file.
What You’ll Need
- Windows administrator account on the SQL Server machine.
- SQL Server installed and the SQL Server service account credentials.
- A valid backup file of the master database (usually master.bak).
- Access to the command prompt or SQL Server Management Studio (SSMS).
- Knowledge of the SQL Server instance name (default is MSSQLSERVER).
Step-by-Step
- Stop the SQL Server service – Open Services (services.msc), find the service named “SQL Server (MSSQLSERVER)” (or your instance name), right-click and select Stop. Confirm it is stopped.
- Start SQL Server in single-user mode – Open Command Prompt as Administrator. Navigate to the SQL Server Binn folder, typically
C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn. Run:sqlservr.exe -m. This starts the instance in single-user mode. Keep this window open. - Connect to SQL Server – Open another Command Prompt (as Administrator) and use sqlcmd to connect:
sqlcmd -S .\MSSQLSERVER -E(use your instance name). If you use a SQL login, add-U sa -P yourpassword. - Restore the master database – In the sqlcmd prompt, run the following command, replacing the path with your backup file location:
RESTORE DATABASE master FROM DISK = 'C:\Backups\master.bak' WITH REPLACE;
Press Enter. The restore may take a few seconds. - Exit single-user mode – After the restore completes, type
EXITin sqlcmd. Then, in the first Command Prompt window (where sqlservr.exe is running), press Ctrl+C to stop the server. Wait for the process to end. - Restart SQL Server normally – Go back to Services, right-click the SQL Server service and select Start. Wait until it is running.
- Verify the restore – Open SSMS and connect to the instance. Run
SELECT name FROM sys.databases;to confirm master is present. Also check that all user databases are visible and accessible. - Restore other system databases (if needed) – If you also have backups of msdb and model, restore them now using normal RESTORE DATABASE commands while the server is running. This is often necessary for a complete recovery.
Common Mistakes
- Forgetting to use WITH REPLACE – The master database is a system database; without REPLACE, the restore fails with an error.
- Not starting in single-user mode – Attempting a restore while other connections exist will cause a timeout or lock error.
- Wrong backup file path – Ensure the path is correct and the file is accessible by the SQL Server service account.
- Restoring an incompatible master – The backup must be from the same SQL Server version and edition. Restoring a mismatched version can cause startup failures.
- Leaving single-user mode active – Forgetting to stop sqlservr.exe -m will leave the server in single-user mode, blocking normal connections.
- Not backing up current master first – Always take a fresh backup of the current master before restoring, in case you need to roll back.
Tips
- Test the backup – Before a real disaster, practice the restore on a test server to ensure the backup is valid.
- Use a dedicated backup location – Store master backups on a separate drive or network location to avoid losing them with the system.
- Document the service account – Know the service account and its permissions; the restore may require specific privileges.
- Keep the backup current – Schedule automatic backups of master after any major change (adding logins, databases, or server settings).
- Use the -m”SQLCMD” option – To prevent accidental connections, start with
sqlservr.exe -m"SQLCMD"so only sqlcmd can connect.
Troubleshooting
- Restore fails with “Database in use” – Ensure no other connections are active. Check with
sp_whoin sqlcmd and kill any sessions. Or restart the service and try again. - SQL Server does not start after restore – This usually indicates an incompatible backup. Restore the original master backup (if you took one) or reinstall SQL Server and then restore your user databases.
- Cannot connect with sqlcmd – Verify the instance name and that single-user mode is running. Try
sqlcmd -Lto list instances. - Error 3154: The backup set holds a backup of a database other than the existing – This means the backup is not of master. Use the correct backup file.
- Error 9001: The log for database ‘master’ is not available – The master data or log file is missing or corrupted. Restore from a known-good backup.
- After restore, logins are missing – Master contains logins. If they are missing, you may need to restore from a more recent backup or recreate them manually.
Frequently Asked Questions
Can I restore master without single-user mode?
No. SQL Server restricts restoring system databases to single-user mode to prevent concurrent access. Attempting otherwise will fail.
What happens if I restore an older master backup?
You will lose any changes made after that backup, including logins, server configurations, and linked servers. User databases remain intact, but may become inaccessible if their metadata is not in the restored master.
Do I need to restore msdb and model as well?
Not always, but if you have backups and want a full recovery, restore them after master. msdb stores jobs and history; model is the template for new databases.
Can I restore master from a different SQL Server instance?
Only if the source and target are the same version and edition. Cross-version restores are not supported and will cause startup failure.
How long does the restore take?
Usually seconds to a few minutes, depending on the size of the master database and the speed of the disk. It is typically small (a few MB).
What if I don’t have a backup of master?
You cannot restore it. You would need to rebuild the master database using the SQL Server setup media (repair or rebuild), then restore user databases from their backups.
Related
General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.
More in this bed
- Show User Library in macOS High Sierra & Sierra
- Device Manager Shortcut: Windows 10 Desktop
- How to Remove Forgotten Passcode from iPhone
- Fix Slow Wi-Fi Problem iPhone X: 9 Steps
- How to Check if a Candidate Accepts Crypto Donations
- How to Listen to Deleted Voicemails on iPhone