Geeks With Blogs
Travis Davies blog

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.

Posted on Wednesday, July 27, 2005 3:56 PM | Back to top


Comments on this post: How to recover a corrupt master MSSQL database.

# re: How to recover a corrupt master MSSQL database.
Requesting Gravatar...
Your article is very useful. Our DBase Server got the Blue Screen of Death after a close lightning strike. It is a striped Adaptec array of 4 drives using SQL 2000 on Windows 2K.

We never resolved what caused the BSOD but I was able to determine thru Recovery Console that the dbase files (.mdf and .ldf) were still there.

So I just added an IDE drive to the machine, installed a new version of Windows on it, and lo, there were all my MDF and LDF files. Installed a new instance of SQL server on the IDE drive and now am about to figure out how to attach the existing files to this new SQL server instance, which is where your aticle comes in handy. I'll report back on the final steps shortly.
Left by Ted Daniels on Aug 13, 2005 5:45 PM

# re: How to recover a corrupt master MSSQL database.
Requesting Gravatar...
I had a similar problem but only with model MSSQL database. So I just copied model.mdf and modellog.ldf from a different PC over mine files and it worked. Make sure you stoped SQL Server service on the other PC before coping files.
Left by Max on Aug 24, 2005 8:07 AM

# re: How to recover a corrupt master MSSQL database.
Requesting Gravatar...
I have the model.mdf file but lost the modellog.ldf file. The customer does not have the original CDs so I have to locate SQL 7.0 (standard) somewhere. I thought I could just replace the file from another server but can't locate a similar server. Does this file stay the same or does it change over time?
Left by Steve B. on Sep 07, 2005 7:39 PM

# re: How to recover a corrupt master MSSQL database.
Requesting Gravatar...
How to restore master database when Restored dirve is different from backup Drive.
Left by AP on Aug 06, 2006 9:25 PM

# re: How to recover a corrupt master MSSQL database.
Requesting Gravatar...
For recover database and db files too,try Recovery Toolbox for SQL as far as i know it has free status,also it repair data from corrupted databases in the MS SQL Server format (files with the *.mdf extension),supports data extraction via the local area network,can save recovered data as SQL scripts, it is also possible to split data into files of any size,compatible with all supported versions of Microsoft Windows, such as Windows 98, Windows Me, Windows NT 4.0, Windows 2000, Windows XP, Windows XP SP2, Windows 2003 Server, Windows Vista,tool supports the following database formats: Microsoft SQL Server 7.0, 2000, 2005,also can repair .mdf files of Microsoft SQL Server 2005, repair mdf file of Microsoft SQL Server 2005 (64-bit).
Left by mdf recovery on Dec 24, 2008 5:30 AM

# Domain Registration website hosting
Requesting Gravatar...
Hi this is domainregs.
We had an old backup of the master database and the .mdf and.
Left by domainregs on Mar 05, 2010 2:24 AM

# re: How to recover a corrupt master MSSQL database.
Requesting Gravatar...
Step 1: Take a full backup of all the System and User databases prior to rebuilding the system databases as we need to restore it later to avoid data loss.

Step 2: Copy the SQL 2005 setup files from the CD to the hard disk. In the command prompt, navigate to the folder which contains the setup.exe file. In my case it is available in D:\Setups\SQL 2005\Servers folder. The SQL Server we are going to rebuild is currently running. Now type the below command,

start /wait setup.exe /qn INSTANCENAME=”MSSQLSERVER” REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=”XXXX”

where XXXX is the name of the password.

INSTANCENAME=”MSSQLSERVER” for default instance of SQL 2005 and
INSTANCENAME=”MSSQL$INSTANCENAME” for named instance of SQL 2005.

For example,
If you have a named instance named as “Deepak\Test” then type as below,
INSTANCENAME=”MSSQL$TEST” in the above command

Refer the below screenshot for the same.

Step 3: After executing the command in step 2 the rebuild process will start and will complete within 5 minutes. You can verify whether the databases are rebuild by navigating to folder containing the data and log files for the system databases. If you arrange them using modified date it will clearly show the time when it was last modified and it is the time when we executed the command in Step 2.

Step 4: Once the rebuild is completed, connect to the SQL server using SSMS. In the object explorer only the system databases will be available.
If any user db were present prior to rebuild it will be lost and we need to perform as below to retrieve it.
Restore from the backup taken in Step 1 (or)
We can attach from the data and log files of the user db as they will not be cleaned up during rebuild process.

NOTE : No Need to detach all the user databases before rebuild as the ldf and mdf files will be present in the same path as usual and will not be overwritten.

Now we need to restore the system databases from the backup which we took in Step 1.
Master database can be restored only in Single user mode (refer Step 5) and other dbs can be restored normally.

NOTE : The ldf and mdf files of the system databases will be overwritten and hence we cannot perform detach/ attach operation.

Step 5: In order to restore master database perform the below steps,
• Stop SQL server and start it using the below command from the command prompt
• NET START MSSQLSERVER /c /m which will start SQL in single user mode
• Type as shown in the below screenshot. Once the restore is completed SQL server will shut down and hence we need to start it normally and access the databases.

Left by Ravi Raj Chelluri on Apr 24, 2010 10:54 AM

# re: How to recover a corrupt master MSSQL database.
Requesting Gravatar...
I was expecting a good MS article explaning the process. Thanks Ravi Raj for your explanantion. It was indeed helpful.
Left by MSQSL DBA on May 24, 2010 11:25 AM

# re: How to recover a corrupt master MSSQL database.
Requesting Gravatar...
hi dears;

i want to (How to recover a corrupt master MSSQL database.)step-by-step in screenshot.

kindly request, i want full details.

Thanks and Regards
k.ganeshan
Left by Ganeshan on Aug 07, 2010 12:25 AM

Your comment:
 (will show your gravatar)


Copyright © Travis Davies | Powered by: GeeksWithBlogs.net | Join free