up vote 4 down vote favorite
1

Recently someone told me I should take a full backup before dbcc checkdb and one after checkdb, even in full recovery. Can someone explain me why I should do this?

I thought a full backup before checkdb was unnecessary (in full recovery) because I can always restore last full backup + diff backup + t-log backups. Is this a wrong assumption?

share|improve this question
up vote 12 down vote accepted

There is no need to take a FULL backup before and after running DBCC CHECKDB. It is recommended that your FULL backup go before the DBCC CHECKDB so that you know exactly which FULL backups don't contain database corruption if DBCC CHECKDB ever fails due to corruption. The FULL backup before the last successful DBCC CHECKDB is one without corruption.

To recover from corruption, you can use the FULL+DIFF+LOG chain or FULL+LOG chain. Pick the FULL backup from before the last successful DBCC CHECKDB. If there was a DIFF before the last successful DBCC CHECKDB, then you can use that too. But a DIFF after the last successful DBCC CHECKDB could have database corruption, depends when the DIFF occurred and when the corruption occurred.

LOG backups do not contain data pages, so they do not have the corrupted data pages in them. Thus, you can recover with no data loss if you have the entire LOG chain and the tail of the log after the non-corrupted FULL or non-corrupted FULL+non-corrupted DIFF.

share|improve this answer
  • Alternatively, if you run CHECKDB immediately before the full backup, then you know the backup is good. Saves you the unnecessary step of backing up a corrupt database. But taking one both before and after is definitely overkill – Gareth Lyons Feb 7 '17 at 18:44
  • 2
    @GarethLyons, a FULL backup after DBCC CHECKDB runs, even if it's immediately after, could contain corruption. It's unlikely, but the corruption could have slipped in after the page was checked and before the FULL backup backed up that page. The bigger the database, the higher the likelihood of it occurring. Still would be rare. But that's why the FULL backup should occur before DBCC CHECKDB. – Tara Kizer Feb 7 '17 at 19:02
  • That's why we verify backups :) You have the same problem when running a backup before checkdb. Why would you want to know your database is corrupt right after backing it up? The backup still contains the corruption. Unless it corrupted between the backup & checkdb, which as you say, is highly unlikely. – Gareth Lyons Feb 7 '17 at 19:23
  • Agreed about testing backups. I come from large organizations that do test backups on a test server. But a FULL backup before the last successful DBCC CHECKDB would not contain database corruption. It could be a corrupted backup file though. Different type of corruption. A FULL backup after DBCC CHECKDB could have database corruption or file corruption. – Tara Kizer Feb 7 '17 at 19:25
  • 2
    @GarethLyons of course, but the full backup that happened immediately after a successful checkdb is probably a lot more valuable, since it is far less likely to have that corruption yet. The full backup right before a checkdb showed corruption is only valuable if the checkdb is what caused the corruption. I don't know that I would put "preventing a wasteful backup operation" very high on the priority list - if we find corruption, we have much bigger problems to solve than the resources used taking a backup we'll throw away. – Aaron Bertrand♦ Feb 7 '17 at 20:48

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.