Rebuild MSDB Database

1.Put MSSQL into single user mode

1.Click -> START -> Microsoft SQL Server 2005 -> Configuration Tools – > SQL Server Configuration Manager
2.Right click on SQL Server and choose Properties
3.Click on the Advanced tab. Under Startup Parameters you will be adding the
following parameters to the beginning of the string: -m;-c;-T3608;



2.Restart SQL Server


3.Connect to SQL server through the Management Console or through command prompt using sqlcmd.
From this point on we will be using TSQL to issue the commands so click the New Query button on the top left. At this point you should be in the master database inside the query window.

Stop reporting or integration services it might stop you to connect in single user mode.


4.Detach the MSDB database using the following commands:

use master
go
sp_detach_db ‘msdb’
go


5.We need to move (or rename, I prefer moving them) the existing MDF and LDF files for the MSDB database so that we can recreate it.


1.Usually these files are located in the following directory:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Your’s might differ.

2.Move (or rename) the MSDBDATA.mdf and MSDBLOG.ldf files.



6.Back to the Management Studio. Open up the instmsdb.sql file in a new query window. This file is usually located in the following directory:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install


7.Execute the file. If you see any errors about xp_cmdshell, just ignore them. They are common and the script will take care of it.


8.At this point you should have your MSDB database restored. The only thing left is cleanup.



9.Execute the following command to make sure that xp_cmdshell is once again set to disable for security reasons:

EXEC sp_configure ‘show advanced options’, 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure ‘xp_cmdshell’, 0
GO
RECONFIGURE WITH OVERRIDE
GO


10.Shutdown SQL Server


11.Go back into your Startup Paremeters for your server in the SQL Server Configuration Manager and removed the -c;-m;-T3608 parameters we added earlier.


12.Restart SQL Server

Everything should be cool at this point and you’ll be able to recreate any Maintenance Plans and Jobs.

Rebuilding or Restoring The SQL Server 2008 Master Database

Rebuild master database in 2008 is different from 2005 and 2000

We don't need externale binary files or setup cd, We can find setup.exe at below location:

"C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release"


Follow the steps below to rebuild the Master Database.


1. From a command prompt window change to the following directory:

"C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release"

2. Next paste the following into the command prompt window and press “Enter”:

Setup.Exe /Action=RebuildDatabase /InstanceName=MSSQLSERVER SAPWD=sa

If master.mdf file is missing manually copy the necessary file(s) from C:\Program Files\Microsoft SQL Server\MSSQL10.ONE2008\MSSQL\Binn\Templates

In case it doesn't work restore the master database on another server and replace the master data and log files on original server and then start repairing from setup file.


Restore latest backup of master and other databases for minimum data loss.

Tablediff utility using SQLCMD

The tablediff utility is used to compare the data in two tables and generate a Transact-SQL script to fix discrepancies at the destination server to bring the source and destination tables into convergence.


/*
Tablediff command from source to destination server with windows login, will give result to DiffsTable and will create query to fix the issue in MyTable1_MyTable2_diff.sql file
*/

EG : Run in SQLCMD mode:

!!"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver ADITYA-PC\ONE2005 -sourcedatabase payroll_AAA1 -sourcetable empmst -destinationserver ADITYA-PC\ONE2008 -destinationdatabase payroll_AAA -destinationtable empmst -et DiffsTable -f F:\MyTable1_MyTable2_diff.sql

Changing Sql Server name

To change the server name first drop the old one:

EXEC SP_DropServer 'OLDNAME'


And now add new server

EXEC SP_AddServer 'NEWNAME','local'


Restart Sql Service for the changes to take place.

Memeber of setupadmin and sysadmin fixed server roles can only execute sp_addserver