Some primary things that should be done on every SQL Server include daily backups and routine scheduled maintenance. Backups are common practice ensuring that if there is data loss you are able to restore it. Maintenance tasks, i.e. integrity checks ensure your database is running as smooth and clean as possible. In this tip, I'll explain the benefit of integrity checks and the process for fixing corruptions. You'll then avoid using corrupted backup files when restoring your database, a not so uncommon practice.
With the database maintenance tasks there are two primary operations: optimization and integrity checks. The optimization component includes rebuilding indexes, defragmenting your data files and where necessary, shrinking your data and log files. These steps ensure you don't waste I/O cycles when retrieving data from the database, which is very important from a performance perspective.
Integrity checks are equally important as this function makes sure there is no corruption within your database files at the SQL Server level. However, this does not check the integrity of your data. The fundamental level entails running the DBCC CHECKDB command which checks the allocation and structural integrity of all the objects in the specified database.
TABLE OF CONTENTS
In addition to running your backups on a set schedule you also need to make sure you are checking for allocation errors on a set schedule. If you are already running these checks – great! But don't forget to review the output from this command for any allocation issues. I have seen several environments where integrity checks are performed by selecting this option in the maintenance plans, but no one ever checks the report to see if there is any data corruption let alone ever fixing the any existing data corruption.
There's a good reason for running these commands, looking for corruption issues and following through by fixing the corruption issues. When you back up your database these corruption issues don't get fixed -- they go right along with the backup file. So if your corruption becomes very bad and your database becomes unstable, a restore is inevitable. Well, when you restore your database by using one of the backup files that was created from this corrupt database, you are restoring the same corruption issues.
Backup and restore musts
At a minimum you should be running integrity checks on a weekly basis if your environment allows for this. For small databases around a GB or so, this process only takes a couple of minutes to run. Or course, the faster the hardware you have the faster this process will run. The DBCC CHECKDB command can run while the database is being used, but because this will impact performance it is better to run at low usage times. Also, if you have experienced any unexpected downtime or hardware failure you should also run these checks to make sure there are no issues.
In addition to running CHECKDB which checks the entire database you can also run DBCC CHECKTABLE for a specific table. If you have a very large database or very large tables, this command can break down the process into smaller steps instead of trying to do the entire database all at once.
Make sure you review the output from this command and look for any allocation and consistency errors. This can be done very quickly by looking at the end of the report for any errors. If there are any issues you can scroll up through the report to find the exact table and index.
Checklist: Fixing the corruption
If you do find corruption in your database you want to take corrective action to eliminate these issues as soon as possible. This can be done by using one of the following options with the DBCC CHECKDB command or DBCC CHECKTABLE command.
- REPAIR_FAST – does minor fast repairs on your database without risk of data loss
- REPAIR_REBUILD – does minor repairs and also rebuilds indexes without risk of data loss
- REPAIR_ALLOW_DATA_LOSS – this is a time consuming process and can result in some data loss.
All of these repair options require the database to be in single user mode, so you will need to have downtime in order to fix the corruption.
In most cases if there is minor corruption these repair options should resolve your problem, but in troublesome situations you may need to handle this one table at a time and rebuild your database table by table and object by object. The following is an outline of the steps you would need to go through:
Put your database in single user mode to stop additional data changes
Create a backup of your database
Restore the backup and use this database to fix the corruption (don't touch your production database)
Run DBCC CHECKDB to find out what tables have integrity issues
For all of the tables that are clean you can move table by table from this restored database to a new clean database
After all clean tables have been moved you will want to tackle the corrupt tables, one table at a time by using the CHECKTABLE command with the appropriate repair option
If the table comes back clean after running the repair option move this table to your new clean database and then go on to the next corrupt table. Continue to do this table by table until you have gone through the entire database
In some cases you may find that after running the repair option that data in a table that you have not fixed yet becomes extremely corrupted. At this point you will need restore the backup again and start the repair process with this table. If this comes back clean continue to fix the remainder of the tables
In severely corrupted databases you have a pretty good chance of recovering a good portion of the data, but you probably won't be able to recover it all. This is an extremely painful and time consuming process to go through, so make sure you are running integrity checks on a set schedule, looking for issues and resolving the issues early on. Based on what I said at the beginning of this article, having all the backups in the world will not resolve corruption issues. Having backups in place is extremely important and you can now see that integrity checks are just as important.
ABOUT THE AUTHOR
Greg Robidoux is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Robidoux, who also serves as the SearchSQLServer.com Backup and Recovery expert, welcomes your questions.