MS SQL Database Repair Guide
Overview of MS SQL Database Repair Guide in Details
There might be a situation when your favorite SQL Database got corrupt and put the stored data at risk. While troubleshooting such corruption issues, you might have read about multiple inbuilt and professional strategies to repair and restore the corrupt SQL database along with their usefulness. The below VikMajra article includes everything about the causes of SQL database corruption along with their symptoms and prevention. In the end, you will get the process to fix corruption through a manual and professional tool.
Let’s discuss some of the common reasons for the corruption of SQL database
This guide is all about common corruption scenarios that arise while working with the DBAs and ways to deal with when corruption occurs along with the prevention measures. Usually, most of the database applications don’t provide enough of a grasp to sufficiently protect against database corruption.
Causes of SQL database corruption
- The most common cause of database corruption (more than 95% of all corruption cases) issues occurs due to hardware issues. It could also be a third-party driver or firmware bug.
- Hardware malfunction, which could be related to Disk Controller, CPU, Communication Channels, Disk Drivers, or Memory Module(s).
- Database corruption due to a power failure in the middle of a transaction.
- Not only disk subsystem failures cause SQL database corruption, but at times, after upgrading a database from SQL Server 2000 to SQL Server 2005 or 2008, etc. you could encounter errors in the database files.
However, fortunately, initially, you will just receive warnings along with some recommended steps to address the issue, such as running DBCC UPDATE USAGE. On top of that, it is also important to check your database regularly with a script or tool that sends the report of all the corruption issues, before it’s too late.
Preventions Steps at the time of SQL Database Corruption and Recovery Process
#1)Check data consistency regularly
It is essential to check data consistency regularly as corruption can go undetected for a long time. Therefore, it is best to use the SQL built-in maintenance plan utility named ‘Check Database Integrity’ or by executing ‘DBCC CHECKDB dbname’ for your databases. It is important to detect and handle corruption as soon as it occurs. It is recommended to run this whenever power failure, abrupt shutdown of the server, can or disk array, or any hint of a glitch takes place in your storage device.
#2)Check the hardware
Whenever you encounter any sign of corruption in your database, it is always recommended that try to check your hardware first. To test your hardware, first, you should primarily check the driver(s), firmware(s), and BIOS versions and make sure they all are running the latest versions. Additionally, you could also run available hardware diagnostic tools to identify hardware issues. There are many utilities available that are typically designed to detect obvious hardware problems.
Although, OEM diagnostic utilities might detect some common hardware failures, in actual these utilities produces a clean bill of health for the hardware that later found the hardware problem.
#3)Regularly monitor Windows Event Logs
To determine any Disk or Hardware related errors you should regularly monitor Windows Event Logs, as it’s not necessary that you detect any corruption as soon as a Hardware Error is reported. At times corruption could be detected even months after a Hardware Error is reported. For example, when a disk suddenly became unresponsive while working with SQL Server it will make the database corrupt but will be detected only when you try to access the corrupt pages, which might take some time say a few weeks or month.
#4)Customize the Antivirus program
At times antivirus programs also cause database corruption issues. Therefore, if you are using an Antivirus application, then make sure your antivirus so not include SQL Server Database Files in its scan.
#5)Avoid storing data on compressed volumes and folders
Rarely compressed volumes and folders used to store database files cause corruption. As a non-supported compressed volume or folder can also be a reason for SQL database corruption; therefore, avoid storing database files in compressed volumes and folders. In the case of less disk space, you can use the data compression utility, introduced in SQL Server 2008. Moreover, avoid storing your database files backups in compressed volumes and folders as well or use the Backup Compression utility introduced in SQL Server 2008.
#6)Use (Product Support Service) PSS
Lastly, Microsoft’s (Product Support Service) PSS is the best way to identify the database corruption along with its causes. PSS always finds out all the evidence of Microsoft Bugs that cause database corruption and fix them as soon as possible. However, always use the latest builds of SQL Server and Windows to minimize the changes in database corruption due to Microsoft Bugs.
In the end, the most recommended way to keep your data prevent from all types of corruption cases is a regular backup on Tapes or other removable media. Since these storage devices are also hardware and might also get failed; therefore we should take the complete backup of our data backup and keep analyzing it regularly. Microsoft offers you various types of backups in SQL Server like Full Database backups, Differential Database Backups, File and File Group Backups, and Transaction Log Backups for your convenience.
It is highly recommended that you should implement a backup strategy on the database as soon as possible if you haven’t done it yet. Additionally, except for the explained methods, there are various High Availability Solutions availed by Microsoft like Log Shipping, Database Mirroring, and Replication, etc. Make sure you are using these High-end Solutions to create a copy of your SQL database in case you encounter database corruption or failure issue.
Repair Options for MS SQL Database Server
Fixing SQL database corruption is the most important thing you should concentrate on after corruption occurs. Here are the basic things you need to know to initiate repair. Although there is no satisfactory solution to fix serious corruption issues in the SQL database, if you are left with no choices, then there are both manual and professional ways available. In order to fix the corruption manually follow the below-mentioned techniques.
Manual Repair of SQL Database
DBCC CHECKDB is the main command of the DBA, used to check the logical and physical integrity of all the objects in the selected database and also fixes the consistency errors in SQL Server databases. DBCC stands either for Database Consistency Checks or Database Console Commands; it includes all the commands to check the consistency of a database.
To check the consistency and integrity of databases regularly DBCC CHECKDB command includes multiple other DBCC commands, like DBCC CHECKCATALOG, DBCC CHECKALLOC and DBCC CHECKTABLE. This means all of these commands don’t need to run separately from DBCC CHECKDB.
2)DBCC command for a Detached Database
To receive the information from a corrupt or inaccessible database that is no longer attached to the primary database (Detached Database), use the command DBCC CHECKPRIMARYFILE.
Note: This command can execute only against a detached MDF file.
To proceed, connect to the SQL Server instance, and run the following query:
DBCC CHECKPRIMARYFILE (‘Full Path to MDF file’, 3)
The result will provide you with three bits of essential information:-
- File ID
- Logical file name
- Physical file name
After a successful run, you will get all the above-explained information that can be used to create a new database. Initially, you need to create a new database with the same database structure as the old one you had attached to the main database. Make sure you have renamed the original database files using appending Old to their filenames before creating the new database.
1)Once you are ready with the new database, stop the SQL Server instance as it might cause SQL Server to release the handles on the physical files. Now you can delete or rename these files. It is recommended that instead of deleting the database files, try to rename them by appending .New to their file names.
2)Again, rename the .Old files back to their original names. Now, we have replaced the files for the new database within the files of older DB. At this point start the SQL Server service, and your old database should now be attached to the main DB.
3)You might have noticed that your database is in suspect mode, but don’t worry this is normal for a corrupt DB. Let’s discuss the ways to deal with suspect DB.
DBCC command for Live SQL Database
Whenever the database comes online to make sure you run DBCC CHECKDB against the affected database to check its consistency. To initiate, run DBCC CHECKDB WITH NO_INFOMSGS. This command will show information about all the corrupt pages along with the possible level of repair. There are 3 basic options:-
1.REPAIR_FAST doesn’t perform any repair actions it maintains syntax for backward compatibility only.
2.REPAIR_REBUILD performs repairs with no possibility of data loss. This command includes the quick fix, like fixing missing rows in non-clustered indexes, and more time-consuming repairs, such as rebuilding an index. Additionally, REPAIR_REBUILD command doesn’t repair errors involving FILESTREAM data.
3.REPAIR_ALLOW_DATA_LOSS is to fix the reported errors but can cause data loss.
The primary purpose of the DBCC CHECKDB command is to check for consistency errors; therefore, you should run it every day. Whenever you find out any inconsistency then the approach to recover the corrupt data should start from the error that you will get after running DBCC CHECKDB, it also helps you to investigate the issue.
In order to fix the corruption you can just restore the database from a backup, but first, check out the process to investigate that which pages are affected and what type of data is affected:
The output from DBCC CHECKDB would look something like this:
The above-explained error message will show you the corrupt page. Initially, to repair it you need to check whether the data is in a heap clustered index or a nonclustered index. You can determine it by check out the output of the DBCC CHECKDB WITH NO_INFOMSGS command. If index ID is greater than 1 then the database is a non-clustered index that can safely be dropped and recreated, but if it is 0 or 1 then there is corruption in your data, and need to perform the repair. Here are the processes you can apply when your database got corrupted.
Restore from a backup
If you are using a FULL or BULK_LOGGED recovery model and regularly maintain a backup, then you can just perform a restore from the last full backup. In case only a few pages are affected you can also go with selectively restoring options that restore only the bad pages.
Repair without backup
If you are using the Simple recovery model and don’t have the backup at all, then automatic repair is the only reliable option you could have. You can run REPAIR_REBUILD.
The syntax is DBCC CHECKDB (‘Your Database Name’, REPAIR_REBUILD).
This is basically to repair minor corruption in your database. If you are lucky enough then it would surely repair most of your minor database issues. Otherwise, you could also use REPAIR_ALLOW_DATA_LOSS to repair all errors. This command can give you the desired results of restoring the database’s structural integrity, but you might lose some of the data.
The syntax is: DBCC CHECKDB (‘DB Name’, REPAIR_ALLOW_DATA_LOSS)
Make sure after running REPAIR_ALLOW_DATA_LOSS you run DBCC CHECKCONSTRAINTS so that you are aware of referential integrity issues and can be resolved using appropriate action.
Limitation: The manual processes are efficient enough to handle minor corruption but when it comes to handling severely damaged SQL databases then usually these processes don’t do justice with them and might end up with data loss or missing.
Use TechSoftwareogic MS SQL Recovery Tool
Finally, if none of the above-discussed solutions work for you; then you need to use VikMajra SQL Database Repair & Recovery Tool on your computer. This professional top-rated SQL recovery software repair corrupted and damaged MDF and NDF files of the Microsoft SQL server and provides immediate recovery to the end customers. Further, it is fully compatible with all the major versions of the SQL database and Windows OS.
Alternative Option to Repair MS SQL Database Server
To restore your corrupt SQL database always use backup, but if this is not something you do with your corrupt database due to lack of backup, then a professional utility is the most reliable and efficient way to deal with database corruption. It is always advisable to use the automatic repair options at the end if you do not have any other choice left as DBCC CHECKDB is a powerful utility, but could be risky if in the wrong hands. Therefore, make sure you have a full understanding of the damage before running automatic repair options. Now, it is your turn! Share your experience with other VikMajra users; which of the above-mentioned commands and SQL recovery solutions works for you? Did you use SQL Repair & Recovery Tool? Also, share this article on social media platforms so that other DBA and professional SQL users can take benefits from this detailed SQL Server Recovery Guide.