Corruption and Copy Backup Files

Question

Corruption and Copy Backup Files

seatedElephant

Say Hey Kid

Points: 674
More actions
January 9, 2014 at 3:44 am

#288106

Hello All,
I've been tasked with creating a backup routine for our data warehouse.
This is the situation at the moment:
Before taking the backups I check for corruption with DBCC CHECKDB. After the backup I use RESTORE VERIFY ONLY with the CHECKSUM option to check the backups validity. Due to certain constraints though, I then have to copy our backups across our network to another servers disk.
My worry is that copying the files over may cause corruption to the backup files during the disk I/O operations and I am not checking for this.
Does anyone know A) if this is a real danger? B) how likely it is?
I'm guessing the need to copy backups between storage devices is not uncommon, but do you risk corrupting the files and hence should you always be checking the validity of the backups after each copy?
Many thanks,
Dave.
Dave Morris :alien:
"Measure twice, saw once"

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

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

HanShi SSC-Dedicated Points: 33506 More actions · Answer 1

By copying (backup)files from one location to another corruption can occur, allthough (most?/all?) copy commands have some kind of internal verification process.

But bottomline is: you can verify your backups by using checksums, etc. but the ONLY WAY to guarantee the validation of a backup is by restoring it!! So the best option is to setup a test-instance where you regularly restore a backup(-chain).

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

seatedElephant Say Hey Kid Points: 674 More actions · Answer 2

Thanks very much for the reply Han Shi. It is interesting what you say about COPY command's internal checks on the data. I'm using ROBOCOPY and hopefully the level of comparison and checking is high with this utility(?)

I would like to have the environment whereby I could do a full restore of all backups for validation and I totally take your point that this is the only way to be 100% sure that you will be able to recover data.

If, though, you were copying backup files (but not able to perform a full recovery check) would you say it was good practice to always verify your backups on the final storage destination device due to the possibility of corruption creeping in during the copy process? Or is the chance of corruption from a copy so slim that most DBAs faced with this would not feel the need to verify after a copy?

Thanks again for your knowledge and your take on this,

Dave.

Dave Morris :alien:

"Measure twice, saw once"

HanShi SSC-Dedicated Points: 33506 More actions · Answer 3

From my experience most DBA don't execute a validation check after a copy action. From my knowledge a corruption won't occur frequently during the copy action. Most corruptions occur during the time when a file is just 'sitting' there and the disk/storage-system gets a bad sector/cluster.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

seatedElephant Say Hey Kid Points: 674 More actions · Answer 4

Hi HanShi,

Thanks a lot for your perspective on this. It reassuring that the copying process is not really an issue.

Dave

Dave Morris :alien:

"Measure twice, saw once"