Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Corruption and Copy Backup Files Expand / Collapse
Author
Message
Posted Thursday, January 9, 2014 3:44 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 10, 2014 9:51 AM
Points: 88, Visits: 319
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

"Measure twice, saw once"
Post #1529271
Posted Thursday, January 9, 2014 6:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:00 PM
Points: 2,224, Visits: 2,657
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’! **
Post #1529310
Posted Thursday, January 9, 2014 7:19 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 10, 2014 9:51 AM
Points: 88, Visits: 319
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

"Measure twice, saw once"
Post #1529332
Posted Thursday, January 9, 2014 8:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:00 PM
Points: 2,224, Visits: 2,657
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’! **
Post #1529372
Posted Thursday, January 9, 2014 8:54 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 10, 2014 9:51 AM
Points: 88, Visits: 319
Hi HanShi,

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

Dave


Dave Morris

"Measure twice, saw once"
Post #1529390
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse