SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Corruption and Copy Backup Files


Corruption and Copy Backup Files

Author
Message
seatedElephant
seatedElephant
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 382
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"
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8208 Visits: 3718
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
seatedElephant
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 382
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
HanShi
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8208 Visits: 3718
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
seatedElephant
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 382
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"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search