Restoring Master and MSDB database in SQL Server

I don't need to mention the significance of Disaster Recovery (DR) plan for System and User Databases in SQL Server. I do test DR plan for User Database very often, but System Database generally I skip. In this post, I want to explain the DR plan and DR Plan testing for System databases. System database restoration is not as similar as User Database restoration. There are broadly three scenario where system database (Master and MSDB ) needs to be restored

(a) Master or MSDB to be restored from a earlier backup to undo the changes
(b) The Server machine crashed and sql server have been reinstalled with fresh copy.
(c) SQL Server is not starting because Master database corrupted
This scenario is well covered in this article


Proposed Backup Plan for System Databases

(a) Full Backup Daily
(b) If any major changes done like replication configuration or creation of Jobs take a backup of system databases after the configuration

You can either go for Hot Backup of Cold Backup of System databases. Hot backup is nothing but taking backup of system databases using BACKUP DATABASE T-SQL Command and you have to restore that backup using RESTORE DATABASE command. Hot backup is an online activity. Cold backup is stopping the SQL Server Engine , Copy MDF and LDF physical files to a backup location and Restart the service. Cold backup requires downtime and generally it is not the case in Production servers.


Scenario 1 : Restore Master & MSDB databases form the Backup file

I have full backup of Master and MSDB. My Master and MSDN databases got corrupted or i did some major changes and i want to rollback. Since i have backup i can restore the system database from the backup.

High level Steps

(a) Stop and Start the SQL Server Database Engine in Singe User Mode
(b) Restore the Master Database from SQLCMD prompt
(c) Start the SQL Server Database Engine
(d) Restore MSDB Database


Windows Start -- Run – Services.MSC --- Enter

You will get this window

























Restore the Master Database from Command Prompt

Windows --- Start --- Run --- CMD --- Enter

















-- If the Screen is not readable this is the command and the result

C:\>SQLCMD
1> RESTORE DATABASE MASTER FROM DISK='D:\MASTER_FULL.BAK' WITH REPLACE
2> GO
Processed 352 pages for database 'MASTER', file 'master' on file 1.
Processed 2 pages for database 'MASTER', file 'mastlog' on file 1.
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.

C:\>


The message says that you have successfully restored Master database.

Note : You have to restart the Database Engine Now






















Restore MSDB Database from Management Studio

RESTORE DATABASE MSDB FROM DISK='D:\MSDB_FULL.BAK' WITH REPLACE



Scenario 2 : Server Crashes – Reinstalled SQL Server Instance

In this scenario, the important point is , you must build the new installation to the same build from where the backup was taken. You have to have same OS Version Edition, Service pack ,Patches and You also must have SQL Server Version , Edition , Service Pack and Patches as the Earlier server from where the backup was taken. Once you build your server to the same configuration the process is same as mentioned in Scenario 1