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
[IMAGE] Looking for corruption
[IMAGE] Backup and restore musts
[IMAGE] Checklist: Fixing the corruption
[IMAGE][IMAGE] Looking for corruption[IMAGE] Return to 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 t...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
o 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.
[IMAGE][IMAGE] Backup and restore musts[IMAGE] Return to Table of Contents
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.
[IMAGE][IMAGE] Checklist: Fixing the corruption[IMAGE] Return to Table of Contents
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.
[IMAGE]
Checklist: Fixing corruption one table at a time
[IMAGE]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:
[IMAGE]
[IMAGE]Put your database in single user mode to stop additional data changes
[IMAGE]Create a backup of your database
[IMAGE]Restore the backup and use this database to fix the corruption (don't touch your production database)
[IMAGE]Run DBCC CHECKDB to find out what tables have integrity issues
[IMAGE]For all of the tables that are clean you can move table by table from this restored database to a new clean database
[IMAGE]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
[IMAGE]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
[IMAGE]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
[IMAGE]
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.
More on SearchSQLServer.com
Tip: Testing SQL Server restores
Tip: Restoring an existing database on the same server
Checklist: Maximize SQL Server backup performance
