Recover MSDB SQL Database Corruption

Today i am gonna discuss here about the problem i have faced in many offices i.e. MSDB corruption. MSDB is very important system database as it stores backup maintenance plan, alert, operators, maintenance plans, backup history, Policy-Based Management settings, Database Mail, Performance Data Warehouse, etc. Obviously it is recommended to backup system databases as well. you can restore system databases easily by creating new system database and and then restoring from the backup (you can Google it). But I am gonna discuss here what if, you don't have any backup file. You can of course create new System databases but you will need to reconfigure you SQL Server in that case and you will have to attach all the databases and create jobs alerts etc (very time consuming process). So I am gonna discuss ans easy and less time consuming process. Here are the steps: -

Stop all the SQL Services and Applications.

Also read :Recover MSDB SQL Database Corruption

Also read :Point of Sale Day End : Statistical data Not Updated SQL Error

Open CMD (command Promt) and type the following command : -

NET START MSSQLSERVER /T3608

• Now detach the MSDB (corrupted database) using this command : - 

"SQLCMD -E -S -dmaster -Q”EXEC sp_detach_db msdb”

• Now browse the Microsoft SQL Server folder in program files-> MSSQL-> data (it will look like"C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA" you can check your own) Now move the msdb file to some other folder. (just in case you need backup)
• Now start all the SQL Services.
• Now in Command prompt enter this command -> 

"SQLCMD -E -S -i” C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Install\instmsdb.sql” -o” C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Install\instmsdb.out”

• Open instmsdb.out file stored in 

"C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Install\instmsdb.out"  using notepad and see if there is any error in the operation.

• create maintenance Plans, alerts, jobs etc.
• Don't forget to backup your system databases now. Happy Trouble-shooting!