Home » SQL Server » How to Rebuild MSDB database?

How to Rebuild MSDB database?

I suggest to have regular full backup of your system database along with user database. But if you do not have system database & your MSDB got corrupted then you have to rebuild it. You can do it by instmsdb.sql script comes with installation. It will result in loss of SQL Agent jobs, backup & restore history, database mail configuration etc. Let’s have a demo for that :-

1. Stop all services connecting to the Database Engine, including SQL Server Agent, SSRS, SSIS, and all applications using SQL Server

2. Start SQL Server from the command prompt using the command: NET START MSSQLSERVER /T3608

3. Detach the msdb database using SQLCMD : SQLCMD -E -S<servername> -dmaster -Q"EXEC sp_detach_db msdb"

4. Using SQL Server Configuration Manager, stop and restart the Database Engine service normally.

5. Now you need to execute instmsdb.sql. You can locate it in <Installation Directory><Instance Name>MSSQLInstallinstmsdb.sql

You can execute it from SSMS as well as from SQLCMD.

For SQLCMD from command prompt use command :

SQLCMD -E -S<servername> -i"<Installation Directory><Instance Name>MSSQLInstallinstmsdb.sql" -o" <Installation Directory><Instance Name>MSSQLInstallinstmsdb.out"

6. Re-apply any service packs or hotfix installed on the instance.

7. Recreate the user content stored in the msdb database, such as jobs, alert, etc.

8. Backup the msdb database.

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Leave a Reply

Join us on Facebook

microsoftcommunitycontributor
%d bloggers like this: