Sunday, January 24, 2016

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.         

No comments:

Post a Comment