How to Re-Create MSDB on SQL 2005

How to Re-Create MSDB on SQL 2005

  •  
  •  
  •  
  •  
  •  
  •  

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.

Published by Aaron Guilmette

Helping companies conquer inferior technology since 1997. I spend my time developing and implementing technology solutions so people can spend less time with technology. Specialties: Active Directory and Exchange consulting and deployment, Virtualization, Disaster Recovery, Office 365, datacenter migration/consolidation, cheese.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.