I am by no means a SQL expert but due to the life of a network engineer I get to see a little of everything. If anyone has any suggestions or comments on the steps I used feel free to make them. 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.