Thursday, September 22, 2011

Recover Corrupt or Damaged SQL Server Master Database

As a SQL server database administrator, you must know how to repair and recover corrupt SQL server data files. SQL server has two data files, MDF & NDF. MDF file is the primary data file and NDF is the secondary data file. MDF file is also known as master database file. The master database file is the main file that contains pointers to all the databases in your SQL server database. Without this file you cannot do any operation on the database or even can't start SQL server services. In this article we will discuss about how to recover & repair mdf (master database file).

Before discussing about different recovery methods, you will have to know what the causes for master database corruption are.

Possible causes for MDF corruption: There are tons of reasons for master database file corruption like hardware problem, power failure, virus attack, software failure, unexpected SQL server shutdown, human mistakes, metadata structure corruption, file system corruption, OS corruption and so on.

How to Recover Master Database: To recover corrupt or damaged master database, you have three methods:
  • Restore from Backup
  • Rebuild Master Database
  • Repair Master Database through SQL Recovery Tool
Restore from Backup: You can restore corrupt/damaged master database from an updated backup by following below steps:

  • First of all create a new master database through rebuild wizard (rebuildm.exe). Rebuild wizard is located in \Program Files\Microsoft SQL Server\80\Tools\BINN directory.
  • Now start SQL server database in single user mode. To do this, open a command prompt and issue the command sqlservr.exe –c -m.
  • After starting in single user mode, restore your master database from backup. You can do this by Query Analyzer or SQL Enterprise Manager.
Rebuild Master Database: If you don't have backup then you can try alternative method that is rebuilding master database. After rebuilt master database you can attach it through enterprise manager or query analyzer.

Repair Master Database through SQL Recovery Tool: If for some reasons above both methods do not work then you can repair corrupt/damaged master database file by using a third party SQL recovery tool. You have to pay some amount but you can recover corrupt master database in just few steps.

No comments:

Post a Comment