What to Do If SQL Server Database Corruption Occurs?

Introduction

When database corruption occurs, operations are performed in such a way so that there will no data loss occur and also reduce the downtime while the recovery process takes place. This means, the extent of corruption in all cases are not same and thus their impact or recovery solution may vary.

What Causes SQL Database Corruption?

Reason #1: SQL Page Corruption:
Information of SQL Server gets saved at page level. A page is the basic unit for storing data and is the smallest level of input/output operations on server. A page may either contain data or details about physical layout of the data. In many cases, the pages in the database may get damaged that store viable data to keep the DB constantly running.

All these pages storing SQL database will have same layout, no matter what kind of data they store. They all will have page header of 96 bytes and a body of 8096 bytes.


Any inconsistency in all of these pages may lead to partial or complete inaccessibility over SQL database. Reason being, these pages either store data in them or acts as a pointer to other pages. A missing page may affect the index of the database.
Reason #2: SQL Page Header Corruption
As discussed above, every page in the SQL database comprise of a header that keeps a control over how information will be saved in the page. While some of the fields of header help to test the consistency of storage structures, some helps to give a smooth navigation to the pages that makes up a table.

Reason #3: Index Corruption
In relational database, an index is an important component that helps in fast retrieval of database from table. Indexes are created for tables and views. SQL Server database can have two types of indexes:
Clustered: A database can only have only one clustered index. This is because it helps in physical sorting of DB.
Non-Clustered: A table or a view can have up to 249 non-clustered indexes. Instead of sorting database, they provide pointer to corresponding index point.

So, damage to clustered or non-clustered index is responsible for partial or entire data loss as it may deviate or block traversal through the database.
Reason #4: Boot Page is Corrupt
A boot page is like the file header in that it provides the metadata information. However, instead of providing information about the data (like header) this page provides information about the database. It is generally located at page 9 in the first data file of the database. It stores information like database ID, the date when database is created, its version, compatibility level etc. It helps in regular consistency checking of the database.

Files Associated with SQL Database?
By default there are two types of files associated with the SQL database:

• Primary database file that saves the database and gets saved in file with .mdf extension. Secondary files can be added to the primary DB and get saved into .ndf file. The secondary does not need to be on same physical drive as the MDF file.
• Another is the log file that keeps a track of all transactions taking place against the database. It gets saved into .ldf file and without it, a database cannot operate. A log file can be called as serial recording of what happened since data was committed to the database.

How to Recover Corrupt SQL Database?

In most of the cases, the root cause of SQL DB corruption is problem at I/O sublevel system, i.e. the issue is associated with the drives, controllers, or the drivers. Therefore, it is always suggested to check for hardware related problems first.
Microsoft has provided Transact-SQL command to check consistency of database. It is known as DBCC CHECKDB which checks the physical and logical consistency of the database. It performs the job of checking links between the pages, index pages, index pointers, structural content etc. The command checks the point of failure and perform recovery. But,sometimes there is possibility of getting this command fail when the mdf file is severely corrupted.
You can go for a professional approach like MDF Recovery Tool to fix SQL MDF/NDF database file. You can either export recovered database object into SQL Server or as SQL compatible scripts.

Conclusion:

Issues like improper shutdown, virus infection, hardware failure are some of the possible reasons of getting SQL Server getting corrupted. The blog discusses various reasons due to which SQL Server database get corrupted, and given its possible recovery solution.