1

We have an instance of SQL Server 2008 R2 SP2 sitting in a DMZ. When I back up one of its databases to a drive local to that server, then run a RESTORE VERIFYONLY against the backup file, it declares the backup set valid.

If I subsequently robocopy the backup file to a mapped drive, pointing to a server located in our domain, then run a RESTORE VERIFYONLY (or a full RESTORE DATABASE, for that matter) against the copied backup file, while connected to a SQL Server instance which is also in the domain (running SQL Server 2008 R2 SP3), the subsequent RESTORE returns the following error message: 

    Msg 3203, Level 16, State 1, Line 1
    Read on "<Backup file path>" failed: 13(The data is invalid.)
    Msg 3013, Level 16, State 1, Line 1
    VERIFY DATABASE is terminating abnormally.

This happens consistently and is repeatable. Anyone have any ideas as to what might be corrupting the copied backup file? Does robocopy feature any validation flags that might be useful? What's the best way to troubleshoot this?

Improve this question
6
  • 1
    How have you validated that the SQL Server service account can see and has sufficient access to the mapped drive? Mapped drivers are per user. Have you set up the same mapped drive for the service account? Why aren't you just using a UNC path (you still need to ensure that the SQL Server service account has access, but it's one less hoop)?
    – Aaron Bertrand
    Nov 18, 2014 at 16:33
  • 1
    Well sounds like flaky connectivity on top of that. Still, you can give a mapped drive to robocopy without ever referencing the path that way to SQL Server.
    – Aaron Bertrand
    Nov 18, 2014 at 16:44
  • 1
    You can use the cmd.exe command fc /b C:\some\local\path\file1.bak \\server\share\path\file2.bak to do a manual binary comparison of the two backup files.
    – Hannah Vernon ♦
    Nov 18, 2014 at 16:49
  • 1
    Aaron, I suspect you may be right about potentially flaky connectivity. Max,thanks for the suggestion to use the FC utility; I'll give that a try.
    – Bob C
    Nov 18, 2014 at 16:51
  • 1
    Just to follow up, the fc comparison did reveal at least 300 byte-pair differences between the original backup file and the robocopied version. I believe a discussion with our network admins will be forthcoming. Thanks again....
    – Bob C
    Nov 18, 2014 at 20:37

1 Answer 1

Reset to default
1

Since the local copy of the backup file is validated successfully via RESTORE VERIFYONLY, the most likely cause of the issue is corruption of the target file while it is being copied over the network.

You can determine if the two copies of the backup file are binary-identical with the command-line utility fc:

fc /b C:\some\local\path\file1.bak \\server\share\path\file2.bak

Alternately, you could generate a cryptographic hash of each file, and see if the hashes are identical. Microsoft offers a command-line tool for that purpose at https://www.microsoft.com/en-us/download/details.aspx?id=11533

If both files are binary-identical, the restore should complete without error.

Improve this answer

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.