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. 

Recovering the MSDB or Model Databases

Situations\scenarios that force you to recover the msdb:
1) You have a production server with lot of jobs\alerts\operators\notifications on it. By mistake you have deleted bunch of jobs\alerts\Notifications and you have no idea what they are. 
2) Non repairable database corruptions on msdb.

And you will be on safe side if you have a recent proper backup of msdb. 

Restoring msdb with a recent backup copy:
In Scenario 1 with Valid Recent Backup: Restoring msdb is very similar to restoring any other user database. You can do it through GUI or Script. And similar to any other user DB you need to gain exclusive access on database. Better stop SQL Server agent which always open 1 or 2 connections to msdb.
                 

In Scenario 1 with more or less equivalent msdb Backup copy from different server:
If you have a more or less equivalent msdb on stage server which you want use to restore damaged (corrupted) production msdb to minimize the msdb data loss and time to recreate all the msdb contents from scratch on production. Restoring process in this scenario is very similar but we have to make sure stage and production SQL server's Versions and Builds are matching. We can only restore a msdb backup copy from different server if both target and source SQL Server Versions and Builds matches. Below is the error that we get if they don't match. 

Msg 3168, Level 16, State 1, Line 2
The backup of the system database on the device C:\msdb.bak 
cannot be restored because it was created by a different version 
of the server (10.50.1600) than this server (10.50.2500).
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

In Scenario 2 with Valid Recent Backup: When the msdb is in suspect mode and non recoverable, GUI doesn't work to restore. We have to  use the restore script to restore the backup. 

Error that we get when we try GUI for restoring a corrupted msdb. 

Below is the restore script to use:

Use [Master] 

GO

Restore database msdb from disk=N'E:\E_MSSQL\MSSQL12.MSSQLSERVER\MSSQL\Backup\msdb.bak' with file=1, Nounload, replace, stats=5

GO

Rebuilding msdb using instmsdb sql script or Templats:
In Scenario 2 with No Latest Backup: In this case be prepared for all the data loss. We only can get the brand new msdb into functioning state by rebuilding it using the instmsdb script. Below are the steps. 

1. Stop the SQL server services.
2. Start the SQL Server services from the command line by running the NET START MSSQLSERVER /T3608. (Trace Flag 3608 Prevents SQL Server from automatically starting and recovering any database except the master database.)
3. Then detach the msdb by running the command SQLCMD ‐E ‐S ‐dmaster ‐Q"EXEC sp_detach_db msdb" . and rename the existing msdb files to something else. 
4. Now run the instmsdb.sql script to rebuild the msdb, SQLCMD ‐E ‐S <SQLSERVERNAME> i"E:\E_MSSQL\MSSQL12.MSSQLSERVER\MSSQL\ Install\instmsdb.sql" ‐o"E:\E_MSSQL\MSSQL12.MSSQLSERVER\MSSQL\Install\instmsdb.o ut", then review the instmsdb.out file for any errors.
5. Then Stop and start the SQL Server in normal mode with no trace flags.  using  NET STOP MSSQLSERVER and NET START MSSQLSERVER. 
6. Reapply any SPs\CUs that are on the SQL Server instance.
7. And create all the lost jobs\alerts\operators\notifications\schedules etc.

OR 

Using the msdb database templates to rebuild the msdb. 

1. Simply stop the SQL Server services. 
2. Copy msdb files from  location E:\E_MSSQL\MSSQL12.MSSQLSERVER\MSSQL\Binn\Templates and overwrite the damaged msdb files at E:\E_MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA,  and restart the SQL Server services.  This will give the same result as rebuilding msdb using instmsdb script.  (Location may vary depending on the installation style)

***** In Case of model database *****: The above mentioned procedures pretty much works with the model database as well. There will be very minmul data loss with model.  If I don't have a latest model database backup, I prefer to use files from templates and later make any necessary changes to database. 

Thing to learn: Make sure your backup methods are covering system databases as well. If necessary take Transactions log backups for system DBs also depending on how frequently your master and msdb will be modified. My suggestion is at least take a daily Full backup of all system DBs.