SQL Server – Recreate System Database – msdb

Background

One of my brothers called me and said that that he is unable to use his beloved SQL Server Management Studio  ( SSMS ) to edit records in a table.

And, that SQL Server says his msdb database his corrupted.

 

Remediate

Restore from Backup

To remediate I asked if he takes backup of the msdb database and he says No.

And, so I know that is not quite a good look.

 

Recreate MSDB from Scratch

Overview

In the sample script below, we will recreate the MSDB Database.

SQL Server comes with a script called instmsdb.sql.  The file creates the msdb database.

We need to make sure that the database and its files do not exist and so we will do some housekeeping and rid self of the existing files.

It is a long winded code and so let us talk about it a bit.

 

Steps

  1. Preparation
    • Base Values
      • Set Instance Name
      • Set Folder Location of the instmsdb.sql file
      • Set Default Folder Location of system database files
      • Set File names for msdb data and log files
  2. Computed Values
    • Set full file name for MSDB Data and Log files
  3. Get Current Timestamp
  4. Compute Backup folder where we will keep the current data and log files for the msdb database
  5. If Backup folder does not exist, create it
  6. Stop MS SQL Server Engine and other SQL Server Services
  7. Start MS SQL Server Engine in minimal mode
    • Trace Flags
      • 3608
  8. Using sqlcmd.exe, detach msdb database
  9. Using sqlcmd.exe, recreate msdb database using instmsdb.sql
  10. Stop MS SQL Server
  11. Restart MS SQL Server in regular mode

 

Code


@echo on
setlocal

Rem set instance Name to Default
set "_instname=."

Rem Set Folder for location of instmsdb.sql
set _installFLD=E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Install

Rem Set Folder for Location of System Database files such as ( master, model, tempdb, msdb)
set _dbSystemFLD=E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\

Rem Set filename for MSDB Database & Log file
set _dbMSDBData=MSDBData.mdf
set _dbMSDBLog=MSDBLog.ldf

Rem Set Full File Name for Data and Log files
set _datafile=%_dbSystemFLD%\%_dbMSDBData%
set _logfile=%_dbSystemFLD%\%_dbMSDBLog%


REM serverfault
REM Need leading zero for batch script using %time% variable
REM Answered by David
REM http://serverfault.com/questions/147515/need-leading-zero-for-batch-script-using-time-variable/529445
:prepare time stamp 
set year=%date:~10,4%
set month=%date:~4,2%
set day=%date:~7,2%
set hour=%time:~0,2%
:replace leading space with 0 for hours < 10
if "%hour:~0,1%" == " "  set hour=0%hour:~1,1%
set minute=%time:~3,2%
set second=%time:~6,2%
set _timeStamp=%year%%month%%day%_%hour%%minute%%second%

set _BACKUPFLD=%_dbSystemFLD%\BACKUP\%_timeStamp%

REM If Backup Folder does not exist, mkdir Backup Folder 
if not exist "%_BACKUPFLD%" mkdir "%_BACKUPFLD%"

REM Stop MSSQLServer
net stop mssqlserver /y

REM Start MSSQLServer
NET START MSSQLSERVER /T3608


REM Using SQLCMD Detach msdb database
sqlcmd  -E -S %_instname% -d master -Q "set quoted_identifier off; if db_id('msdb') is not null begin exec sp_detach_db 'msdb'; end "


REM If MSDB Data & Log file already exist at target folder, pleaes move to backup folder 
if exist "%_datafile%" move "%_datafile%" "%_BACKUPFLD%"
if exist "%_logfile%"  move "%_logfile%"  "%_BACKUPFLD%"


REM Using SQLCMD run instmsdb.sql
SQLCMD -E -S %_instname% -i "%_installFLD%\instmsdb.sql" -o instmsdb_%_timeStamp%.log


REM Stop MSSQLServer
net stop mssqlserver /y


REM Start MSSQLServer
NET START MSSQLSERVER


endlocal


Source Code Control

GitHub

Here is the URL for our repository

 

References

  1. Database Features > Databases (Database Engine) > System Databases > Rebuild System Databases
    Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s