Database Integrity Checks on Database Backup.

  • In theory, is it possible to rely on a restored version of a database to perform Integrity Checks on. What we have is a problem where our main Database is so heavily hit that running a DBCC Checkdb causes blocking. What I want to do is restore this DB into a new location (on the same server) and run integrity checks against this. Is this providing any real integrity checking, or is it possible this method could lead to me seeing no corruption in the restored DB, whilst corruption exists in the live db?

  • Are you pegged 24/7?

    If not you could do a dbcc checkdb in the wee hours


  • that is a good idea.

    The only think that I would do ..instead to recover in the same PC I would recover in different one because the memory and CPU will be affected when you run DBCC that will affect the live database

    try that

    the chanses to have an DB corruption in live and not catch it in test is minimal;if you backup and immediately you restore in testiong.

  • If your DBCC checks would fail then chances are your not going to be able to restore the least not cleanly. This is one of the reasons it is good to run DBCC checks prior to a backup.

    I too have a database that I can not run a DBCC CHECKDB on. It is a 24x7 300 GB. I am fortunate enough to be able to create a standby database within minutes using a snapshot with exact copies of the files. This is with the EMC TimeFinder utility on a SAN.

    Some things you may want to think about is breaking up the DBCC CHECKDB command with the DBCC CHECKALLOC and a DBCC CHECKTABLE statements. I have also read somewhere that these checks can be performed at the filegroup level. If your filegroups are laid out with some granularity it may be possible to run the checks on the database.

    Not sure if any of this helps, but thought I would throw a couple of cents in.

    "Keep Your Stick On the Ice" ..Red Green

  • I would also recommend checkalloc if you can't fit a checkdb in a normal timeframe. If checkdb won't run in a reasonable amount of time what do you do for index rebuilds or defrags?

  • Thanks guys, your input is much appreciated. Just to clarify, the reason for this issue arising is that we only have an absolute maximum of 15 minutes per day when we are allowed to do anything detramental to the system, despite many arguements over the point. Thus with a Full Backup taking priority during this time, along with any index issues, I am trying desperately to come up with the best plan of action re Integrity Checking!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply