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.
by
·
Join the DZone community and get the full member experience.
Join For Free“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.
Published at DZone with permission of Andrew Jackson. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments