Home > Resources > SQL Server > Restore Master Database in SQL Server 2012

How to Restore an Unavailable Master Database in SQL Server 2012

SQL Server couldn’t start because of master database unavailable. How can SQL Server 2012 restore database, especially master database, the most important SQL Server database?

Master database records all the system-level information about SQL Server system. This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings. And master database is the database that records the existence of other databases and the location of those database files records the initialization information for SQL Server. Therefore, SQL Server cannot start if master database is unavailable.

The following passage would intend to talk about how to restore a master database with database backup in single user mode.

Restore Master Database with SQL Query

Step 1: Start the server instance in single-user mode.

Open Control Panel – System and Security – Administrative Tools – Services – MSSQLSERVER.  

open MSSQLSERVER services

In MSSQLSERVER Properties window, stop this service. And set "Start parameters" as "-m". Then Start service again. Now SQL Server would start in single user mode.

set SQL Server start in single user mode

More detailed information, please refer to: How to Set SQL Server Access with Single User Mode

Step 2: Open but not login SQL Server 2012 Management with Administrator, and click "New Query".

Step 3: Restore master database with database backup

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 instructs SQL Server to restore the specified database even when a database of the same name already 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.

Master database restoration is the necessary step to reset SQL SA password with tool when SA password is forgotten and master database is not available.

Tips:

1. Always have a current backup of the master database available.
2. Back up the master database as soon as possible after the following operations:

3. Don’t create user objects in master database. Otherwise, master database should be backed up more frequently.
4. Don’t set TRUSTWORTHY "ON" especially for master database.

Related Articles:

Hot Categories
Windows Password
Product Key Finder
SQL Password
More +
Hot Articles
4 Ways to Change SQL Server SA Password
SQL Server 2012 SA Password Reset
How to Login SQL Server after SA Account Locked Out
How to Change Server Authentication Mode in SQL Server
How to Enable Mixed Mode Authentication for SQL Server
More +
Latest Articles
How to Correct SQL Login Failed Error after SA Password Forgot
Solve SQL Server Connection Error 26
How to Fix Login Failed to SQL Server
More +
Hot Products
Windows Password Genius
iTunes Password Genius
Product Key Finder
More +