0

I was trying to copy large database backup files(appx. 2TB each) to different region using robocopy. But I have noticed the speeds are drastically slow when using the switch /Z. So I have removed this switch and my copy is now faster. But now the challenge is,

  1. how to verify the consistency of copied files.
  2. what is the best possible way to skip the files if the files are already present in target location.
  3. I have tried using powershell get-filehash command to verify file consistency but it’s too slow on large files especially on files located in different region.

My current command syntax,

robocopy source_directory destination_directory file_name /R:3 /W:10 /LOG+:log_file_location

Any best possible ways to address this?

  • what kind of database? SQL Server, MySQL, Oracle???? – kevinsky Jan 20 at 18:59
  • Primarily SQLserver, but I’m looking for a general solution which is applicable to any large file. – sqlcheckpoint Jan 20 at 19:01
  • The scope of your question may be too large or not even on topic. Verifying file consistency if more of a stack overflow or even super user question. A general solution for any type of database is too general. Please be more specific – kevinsky Jan 20 at 19:04
  • @kevinsky the primary reason I have posted in this group is the solution I’m trying to find is for SQL server database backups. Probably some DBA should have come across this kind of situation. – sqlcheckpoint Jan 20 at 19:08
  • suggest you to use compression and stripe to multiple files - this will help in faster copy as well. Restore verifyonly should be use to verify if you will be able to restore the files. Also /Z = restartable option is slow ! If you have a stable network - dont use /Z option. – Kin Shah Jan 21 at 3:21
2

Two ways verify consistency come to mind:

  • Perform checksums on each "end" of the copy (you've already tried this)
  • Restore the database after copying the backup files (if this is production data, you will need to be licensed appropriately as verifying those backups would probably be considered a production workload). There are myriad ways you can automate restoring & performing a dbcc checkdb, to varying degrees of detail (the RESTORE VERIFYONLY method may or may not be sufficient for you)

You can skip re-copying files using robocopy's mirroring functionality. I use the following to copy my backups to another location, suppressing all output:

robocopy SOURCE DESTINATION /M /COPY:DAT /E /DCOPY:DAT /MT:128 /nfl /ndl /njh /np /ns /nc /njs

Files that already exist on the destination won't be re-copied, and the directory structure will be maintained.

3

SQL Server has a very handy command which checks the headers, checksums (if present) and that the backup set is complete. A data integrity check will be performed if a checksum is present but is optional. From the documentation here

RESTORE VERIFYONLY FROM DISK = 'D:\AdventureWorks.bak' WITH CHECKSUM;
GO
  • This is the right solution but I need to have a separate instance to verify this to avoid resource utilization or any accidental restores on production. – sqlcheckpoint Jan 23 at 19:36
  • @sqlcheckpoint that's fine, just be aware that the instance where you're doing that verification has to have a production license as verifying production backups is considered a production workload. – alroc Jan 23 at 20:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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