Geek with special skills

Rebuild MSDB Database

If you accidentally delete the transaction log file of msdb database on a newly installed SQL Server 2008 R2 instance do the following steps

I searched Books Online and found this article about Rebuilding System Databases, which helps in rebuild the msdb database.

Steps to Follows

  1. Stop all the SQL Server services & start the command prompt with elevated administrative privilege & execute the following command:
  2. Once you start the SQL Server with trace flag 3608, you will be able to detach the msdb database. To do that, execute the following command in SQLCMD mode:
    SQLCMD -E -S DBS03 -dmaster -Q"EXEC sp_detach_db msdb"
  3. Rename the msdb data file, and execute the instmsdb.sql file from the install folder, as shown below:
    SQLCMD -E -S DBS03 -i"E:\SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Install\instmsdb.sql" -o"E:\instmsdb.out"
  4. Review the instmsdb.out for any errors and re-apply the service packs.
  5. Stop the SQL Server.
  6. Start the SQL Server normally


Since I was able to connect to the instance without any error, I stopped the SQL Server instance and copy all the system databases files. Later I restarted the SQL Server Agent and the instance was online.

Hope, this may help someone, Happy Learning Smile


06/19/2012 - Posted by | SQL Scripting, Sql Server, T-SQL | , ,

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: