SQL Server Database Restore Failing on Server on Another Network
This is one different issue I found in MSDN forum, thought to BLOG it so that it can be helpful to others as well who face this kind of issue.
SQL Server 2008 R2 database backup was performed on a server and then the backup file is copied over to a VMDK disk which is accessible to the server. Later this VMDK file is attached to another server in a different network which does not have access to the server where backup was performed. Backup file is now copied from the VMDK server into the new server and when Restore of database is attempted, then the restore runs for some time and then throws an error that the restore operation failed. Below is more detailed error message returned by the restore.
Msg 3203, Level 16, State 1, Line 1
Read on “D:\TestDB.bak” failed: 13(The data is invalid.)
Msg 3013, Level 16, State 1, Line 1
Restore database is terminating abnormally.
Initially the suspect was that the backup may be corrupt, so the backup file was copied to another server directly without the use of VMDK and the restore was successful, so this narrowed down the problem to be more related to copy of the backup file from server to VMDK and from VMDK to new server, some where it is getting corrupted.
The solution to this problem was to copy the backup file by using the option /j for Xcopy command
/j option Copies files without buffering. Recommended for very large files. This parameter was added introduced in Windows Server® 2008 R2.
Once the backup file was copied using the /j option there was no more corruption related to the backup and the restore completed successfully.
Read on failed: 13(The data is invalid.) error generally occurs if the backup file is corrupted or the disk where we are trying to backup or restore is having consistency problems.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.