Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

SQL Database Corruption: Causes, Prevention, and Fixing Techniques

DZone's Guide to

SQL Database Corruption: Causes, Prevention, and Fixing Techniques

If you're facing a corrupt MS SQL database, then you'll want to have a look at this post for some tips and help.

· Database Zone ·
Free Resource

Download the Altoros NoSQL Performance Benchmark 2018. Compare top NoSQL solutions – Couchbase Server v5.5, MongoDB v3.6, and DataStax Enterprise v6 (Cassandra).

“I had a Database File and I want to attach this MDF file to SQL Server 2014. However, whenever I try to attach the file, I am receiving following error message :
The header for file ….\MSSQL\DATA\xxxx_data.mdf’ is not a valid database file header. The FILESIZE property is incorrect. (SQL Server Error 5172). After surfing a lot, I found that MDF file has corruption in its header. I tried expert solution given in various forum websites but failed to fix header corruption, I am still getting the same error message. I have no idea how to deal with SQL database corruption. Can anybody suggest me any solution to fix this error? Thank you in advance!”

SQL Server stores its physical database in MDF file and the first page of this file comprises the information of MDF file header. The header page keeps the information about the entire database like file signature, size etc. While attaching the MDF file in SQL Server, sometimes users encounter 5172 error code. This error generally occurs when the MDF file becomes corrupted or damaged. This error mismatches the information of the header and takes database into the inaccessible state. Thus, in this problem tackling blog, we are going to describe some easy and cost-efficient ways of dealing with a SQL database corruption.

Before moving on to the solution to fix the database corruption, let us discuss what steps should not be taken if you are facing a SQL database corruption.

What Not to Do?

Do Not Reboot Server

Rebooting the server can only help to fix the minor issues from the OS end. If the issues are from the server's end, then rebooting is not an appropriate solution. Rebooting the system will put the database in offline mode and will detect it as a SUSPECT. This will make the situation worse.

Do Not Shutdown SQL Server

After identifying the master database corruption in SQL Server, every user tries to shut down the SQL Server, yet this is not a proper solution, as the database becomes inaccessible if this is done. 

Do Not Detach/Re-Attach Database

If corruption is present in the database, then detaching and re-attaching the database will make the recovery process much harder.

Do Not Upgrade SQL Server

Upgrading the SQL Server to fix the SQL database corruption is not an accurate method because upgrading the SQL Server version can create a new and major hurdle.

Do Not Run Repair Command Instantly

The DBCC CHECKDB command should only be run when all other methods fail. In many cases, this command takes users to permanent data loss condition. Thus, before executing this command, make sure you have correct knowledge of its syntax.

Solution to Fix SQL Database Corruption

Keeping regular backups and utilizing them later to recover the database in case of corruption or other disasters is the best option to deal with a SQL database corruption. Nevertheless, it is not always a possible solution to restore all the data, as there is always a missing portion of information between the last backup and time of disaster.

The Final Note

There could be various factors that are responsible for the corrupt database like sudden shutdown, hardware failure, virus attack, etc. In this write-up, we have covered various causes responsible for the corruption and what steps should not be taken when your database is in SUSPECT mode. The article covers a quick solution to repair corrupt SQL database file.

Download the whitepaper, Moving From Relational to NoSQL: How to Get Started. We’ll take you step by step through your first NoSQL project.

Topics:
mssql ,database ,corruption ,tips ,fix ,solution

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}