Posted on by Aaron Guilmette

How to Re-Create MSDB on SQL 2005

       
Be the first to vote!

From time to time, I’ve found myself in a situation where I’ve needed to re-create MSDB. Unfortunately, most of the guides on how to do it give just basic overviews, leaving the unlucky admin muddling around.

Here is a real step-by-step for SQL Server 2005.

  1. Shut down SQL Server.
  2. CD to the SQL installation directory, such as %ProgramFiles%\Microsoft SQL Server\MSSQL.1\MSSQL\Binn and run start sqlservr.exe -c -T3608 or go into the Services applet, select the SQL Server service, and enter -c -T3608 in the parameters text area, and click Start.
  3. Open SQL Management Studio.
  4. Expand Database > System Databases, right-click Master and select New Query.
  5. Type sp_detatch_db ‘msdb’ and press F5 to run.
  6. Locate the MSDB files (usually msdbdata.mdf and msdblog.ldf) on your server; typically in %ProgramFiles%\Microsoft SQL Server\MSSQL.1\MSSQL\Data. Move/rename them.
  7. Navigate to %ProgramFiles%\Microsoft SQL Server\MSSQL.1\MSSQL\Install and run instmdb.sql by double-clicking it.
  8. Shut down SQL server service and restart it normally.

Of course, replacing/recreating MSDB means any of the things stored in it will be lost (maintenance schedules, activity logs, database mail, and such).  But, it’s better than not being able launch the admin studio.