This morning we ran across a MSDB database that was corrupt with no know working backup. We needed to bring this database back to a working order so we could properly maintain our clients SQL 2005 Server properly. After several attempts trying to start the SQL server in recovery mode on the MSDB database from the command line we had to use a combination of command line and Queries to complete our task.

  • First we shut down the SQL server
  • net stop mssqlserver
  • Then we ran our flag from the BINN directory
  • start sqlservr.exe -c -T3608
  • Next in SQL Server Management Studio we detached the MSDB database
  • use master go sp_detach_db ‘msdb’ go Then click Execute
  • We then moved the MSDB mdf and log file to a backup directory
  • C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data Your directory may differ
  • Back to the command line we ran instmsdb.sql – Which opened up SQL Server with a new Query (Execute this Query)
  • C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install Again your directory may differ If you see errors about xp_cmdshell, it is safe to ignore them.
  • Your MSDB database is now restored. Now follow through with some clean up
  • You may want to disable xp_cmdshell for security reasons. If so, you can now execute the following.
  • GO RECONFIGURE WITH OVERRIDE GO EXEC sp_configure ‘xp_cmdshell’, 0 GO RECONFIGURE WITH OVERRIDE GO
  • Shutdown and start the SQL Server
  • net stop mssqlserver
  • net start mssqlserver