Thursday, November 4, 2010

How to recover a corrupt master MSSQL database.

Recently we had an issue with a client that there master database was corrupt and MSSQL would not start. We had an old backup of the master database and the .mdf and .ldf of the client databases only. Below I have listed out the steps we took to recover the master database and the client databases.

- We started sqlservr.exe as an application using the trace flag -T3608 and it gave the error:

Error: 9003, Severity: 20, State: 1.

Cannot recover the master database. Exiting.

- We then proceeded with reinstallation of sql server for a named intance:NEW. and also applied sp3.

- Started sqlservr.exe -c -m -snew

- Restore database master from disk = 'D:\SQLDATAold\mssql\BACKUP\master_db_200503130200.BAK' with replace

- Started sqlservr.exe -T3608

- Checked the consistency of the Master database using: dbcc checkdb ('MASTER') - zero consistencies

- Next detached the Model database :sp_detach_db 'model'

- Renamed the new instance model db files

- Attached the Model database using: sp_attach_db 'model','D:\sqldata\MSSQL$NEW\Data\model.mdf','D:\sqldata\MSSQL$NEW\Data\modellog.ldf'

- Then took care of the Temp db by : Alter database tempdb modify file (name= 'tempdev', filename = 'D:\sqldata\MSSQL$NEW\Data\tempdb.mdf')

Alter database tempdb modify file (name= 'templog', filename = 'D:\sqldata\MSSQL$NEW\Data\templog.ldf')

- Detached the MSDB database: sp_detach_db 'msdb' and attached it back pointing to the right location:

sp_attach_db 'msdb','D:\sqldata\MSSQL$NEW\Data\msdbdata.mdf', 'D:\sqldata\MSSQL$NEW\Data\msdblog.ldf'

- Then changed the server name by executing the following commands

- select @@servername

- sp_dropserver 'BTLAPP'

- sp_addserver 'BTLAPP\NEW', 'LOCAL'

- Attached the DOCUWARE database and checked its consistencies: dbcc checkdb ('DOCUWARE') - zero consistencies

This worked really well the only drawback is the new instance name of MSSQL. We had to change the ODBC on every client workstation to point to the new name.


  1. For settling out troubles with invalid mdf files you can use repair mdf. It uses contemporary ways of restoring dbx files, the application starts under all PC software configuration and Windows OS. It demonstrates results of working with bad .mdf files.

  2. For resolving SQL error 9003 you must use SQL recovery tool which is very helpful to fix SQL error & repair damaged SQL database. Get more info from