Sql-server – Restore from copy of SQL Server backup file produces invalid data error

backupcopyerrorssql server

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?

Best Answer

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.

Related Solutions

Sql-server – SQL Server 2008 – Restore Database With RESTART

Has this ever worked?

RESTART is only specified when you want to continue an interrupted restore. If you can't change the SQL, then you can only somehow break a first restore to generate an error condition.

Because of this, you can't be sure that it's the same on SQL 2005 unless it happens every time. Or they changed behaviour.

Sql-server – SQL Server 2008 Restore

Try this:

CREATE DATABASE foo;
GO
CREATE DATABASE bar;
GO
BACKUP DATABASE foo TO DISK = 'c:\temp\x.bak';
BACKUP DATABASE bar TO DISK = 'c:\temp\x.bak';
GO
RESTORE HEADERONLY FROM DISK = 'c:\temp\x.bak';

Relevant columns for this scenario:

Position  DatabaseName
--------  ------------
1         foo
2         bar

To restore foo, whether it exists or not:

RESTORE DATABASE foo FROM DISK = 'c:\temp\x.bak'
  WITH FILE = 1, REPLACE;

To restore bar, whether it exists or not:

RESTORE DATABASE bar FROM DISK = 'c:\temp\x.bak'
  WITH FILE = 2, REPLACE;

If you don't specify WITH FILE, you will get a slew of errors, depending on whether the database in Position = 1 already exists.

The lessons here:

do not use common and generic names for backup files - name them for the database they represent and keep them separate.
use WITH INIT if you expect a backup file to only ever contain one copy of a backup.
understand what is in your backup by first running RESTORE HEADERONLY and/or RESTORE FILELISTONLY.

If you show us those two columns from the output of RESTORE HEADERONLY and what database you are actually trying to restore, we can provide you with a specific command that should work.

Best Answer

Related Solutions

Sql-server – SQL Server 2008 – Restore Database With RESTART

Sql-server – SQL Server 2008 Restore

Related Question