Monday, February 23, 2009

How to Re-Create MSDB

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.

No comments:

Post a Comment