Rebuild MSDB Database

1.Put MSSQL into single user mode

1.Click -> START -> Microsoft SQL Server 2005 -> Configuration Tools – > SQL Server Configuration Manager
2.Right click on SQL Server and choose Properties
3.Click on the Advanced tab. Under Startup Parameters you will be adding the
following parameters to the beginning of the string: -m;-c;-T3608;



2.Restart SQL Server


3.Connect to SQL server through the Management Console or through command prompt using sqlcmd.
From this point on we will be using TSQL to issue the commands so click the New Query button on the top left. At this point you should be in the master database inside the query window.

Stop reporting or integration services it might stop you to connect in single user mode.


4.Detach the MSDB database using the following commands:

use master
go
sp_detach_db ‘msdb’
go


5.We need to move (or rename, I prefer moving them) the existing MDF and LDF files for the MSDB database so that we can recreate it.


1.Usually these files are located in the following directory:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Your’s might differ.

2.Move (or rename) the MSDBDATA.mdf and MSDBLOG.ldf files.



6.Back to the Management Studio. Open up the instmsdb.sql file in a new query window. This file is usually located in the following directory:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install


7.Execute the file. If you see any errors about xp_cmdshell, just ignore them. They are common and the script will take care of it.


8.At this point you should have your MSDB database restored. The only thing left is cleanup.



9.Execute the following command to make sure that xp_cmdshell is once again set to disable for security reasons:

EXEC sp_configure ‘show advanced options’, 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure ‘xp_cmdshell’, 0
GO
RECONFIGURE WITH OVERRIDE
GO


10.Shutdown SQL Server


11.Go back into your Startup Paremeters for your server in the SQL Server Configuration Manager and removed the -c;-m;-T3608 parameters we added earlier.


12.Restart SQL Server

Everything should be cool at this point and you’ll be able to recreate any Maintenance Plans and Jobs.