Using the msdb database templates to rebuild the msdb. And create all the lost jobs\alerts\operators\notifications\schedules etc.Reapply any SPs\CUs that are on the SQL Server instance.using NET STOP MSSQLSERVER and NET START MSSQLSERVER. Then Stop and start the SQL Server in normal mode with no trace flags.Now run the instmsdb.sql script to rebuild the msdb, SQLCMD ‐E ‐S 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.and rename the existing msdb files to something else. Then detach the msdb by running the command SQLCMD ‐E ‐S ‐dmaster ‐Q”EXEC sp_detach_db msdb”.(Trace Flag 3608 Prevents SQL Server from automatically starting and recovering any database except the master database.) Start the SQL Server services from the command line by running the NET START MSSQLSERVER /T3608.We only can get the brand new msdb into functioning state by rebuilding it using the instmsdb script. In Scenario 2 with No Latest Backup: In this case be prepared for all the data loss. Rebuilding msdb using instmsdb sql script or Templats: We have to use the restore script to restore the backup. In Scenario 2 with Valid Recent Backup: When the msdb is in suspect mode and non recoverable, GUI doesn’t work to restore. RESTORE DATABASE is terminating abnormally. The backup of the system database on the device C:\msdb.bakĬannot be restored because it was created by a different version
Below is the error that we get if they don’t match.
We can only restore a msdb backup copy from different server if both target and source SQL Server Versions and Builds matches. 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. 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. In Scenario 1 with more or less equivalent msdb Backup copy from different server:
0 kommentar(er)
