Recovering Master Database

Reasons that force you to recover (Rebuild and\or Restore) the master database:

As we all know master database is very important system database. The master database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server, SQL server wont even start without master database. Master database contains instance-wide metadata like logon accounts, linked servers, endpoints and system configuration settings.

1) Deletion of any important logins, linked servers or any other SQL object that gets stored in master database.

2) Damaged and unusable master database may be because of disk failure or software failure. 

In case 1 we can get back to normal state by restoring the master database from a latest backup copy.

In case 2 we need to rebuild the master database which is nothing but recreating a whole new SQL Server and then restore the system databases and user databases using good latest backup copies.

Restoring the master database from a backup copy:

Let's take I am with case 1. I have my SQL Server looks like something like below with some test logins\Linked servers and always on setup:

I have taken a backup of my master DB and now I am deleting all my link servers and logins to see how I can recover them from the backup that I took. My SQL Server looks like below after deleting those objects:

Step 1: We need to open SQL Server Configuration Manager and right click on SQL Server service to edit the properties to add Startup Parameter -m then click Add, Apply and OK and restart the SQL Server services, this will start the SQL Server in single user mode.  We can't restore master database when SQL Server running is not running in single user mode. 

Step 2: Now open the Command Prompt and run SQLCMD.exe with switches -S (SQL Server Name \Instance Name), -U (Login name) -P (password), depending on where you are running it from. When I did my demo I was RDP'd to the box so I didn't gave switches. And it opens a connection to the SQL Server, enter the T-SQL to restore master database as below. You will finally see a success message as underlined. 

Remember only Sysadmin account can connect in Single User mode. If you have trouble executing SQLCMD.exe from command prompt, you can browse to the location E:\Microsoft SQL Server\110\Tools\Binn where you can see SQLCMD.exe.

Step 3: As final step “go to SQL Server Configuration Manager and remove the Startup Parameter -m and restart the SQL Server service”, that's it now we should able to see all the deleted linked servers\logins. 

Rebuilding the master database:

Let’s take I am with case 2. My master database is so badly corrupted which not even allowing the SQL Server service to start. For demo purpose I have managed to corrupt my master database log file (mastlog.ldf). My healthy SQL Server looks something like below. I have taken the backups of all my system databases, as I need to restore system databases after rebuild them. 

And I have stopped my SQL Server services and I have corrupted my Master database ldf file. And tried to start my SQL Server services as expected it didn't start with the below error.

And looked at the Windows Event Viewer application logs, I found below errors which clearly points to the master database corruption. 

Even I tried to start SQL Server in minimal configuration mode with startup parameter (-f) as a last resort that didn't work either. Now we are left with only one option rebuilding master database. 

Rebuilding process:

I have my Setup Bootstrap in C drive at below location, you will see the Setup.exe inside the Setup bootstrap folder under the SQLServer2014 folder (this folder name changes by version). Open the command prompt as administrator, browse to the folder and run the setup.exe with following the parameters. Replace Admin_Account\Admin_Account_Password with account\Password that has sa rights, InstanceName with MSSQLSERVER for default instance or Name of the instance for named instance. 

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME="InstanceName" /SQLSYSADMINACCOUNTS="Admin_Account" /SAPWD= "Admin_Account_Password"

In my case the rebuilding process has last for around 5 mins, and you can find the detailed summary of the rebuilding process with final result in summary.txt in the log folder under Setup bootstrap folder. After rebuilding process my instance looks something like below with no logins, User databases. Even the system databases are just empty shells.  

Now it’s time to restore the master, msdb and model databases from the backups. As I said earlier I have backed up all my system databases right before corrupting my master database. First start with restoring the master database use the case Restoring the master database from a backup copy from the above tip in this same post. Then restore msdb and model using my previous post from here.  And my user databases got recovered as soon as my master database is restored from backup, as the user Databases information already exists in my master database.

And let’s say you don't have a master database backup copy and you have corrupted master database. Before you start fixing it try to save the msdb and model db mdfs and ldfs to a safe location. Now try rebuild the master database and you need to attach all the user databases manually and create all the logins, linked servers etc. And then stop the SQL Server service and replace the newly built empty msdb and model database files with the files that you saved earlier and restart the SQL Server services, this way at least you can save time on recreating jobs and other msdb and model database related stuff.