none
Weird: Backup file works fine from the network but not when moved into sandbox environment

    Question

  • We have a valid full backup of a database. We know it is valid, we have restored it twice from the network with no problems, but we do not have access to the network location from our sandbox environment.

    The .bak file is sizable at about 9GB. The .bak file resides on our internal network, in a SAN drive. No problems there. When we copy the file from there into a sandbox environment to attempt the restore in the sandbox environment it gets corrupted. We've tried three different times and all three different times it gets corrupted. First time we copied the file over to the sandbox the restore went up to 50% and failed. The second time we copied the file again and attempted the restore again it failed at 70%. The third time it failed at 60%. The error message we get during the restore is "...Read on ... failed: 13(The data is invalid) Msg 3013, Level 16, State 1, Line 1 Restore database is terminating abnormally."

    Now some background here. To move the file our network team is doing this: they have this .vmdk file that they mount out in our production environment (which has access to the network location where the .bak file is), copy the file into the drive, then move the .vmdk file into the sandbox environment(which does not have access to the network location), mount the drive in the sandbox environment, and then I have access to the .bak file from within the sandbox environment.

    Something in the process of using the .vmdk file to move the .bak file from production into the sandbox is causing the file to get corrupted.

    Any thoughts? Has anyone seen this before? Any idea? TIA,

    Raphael


    rferreira

    Wednesday, December 03, 2014 4:02 PM

Answers

  • Hello,

    I am guessing this has to do with the network copy using buffers and the vmdk being moved around. Try using something such as xcopy with /J and trying again.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    • Marked as answer by rferreira.dba Thursday, December 04, 2014 2:55 PM
    Wednesday, December 03, 2014 4:11 PM
    Answerer

All replies

  • Please post the results of SELECT @@VERSION from both servers.

    This is more likely a bug in SQL Server, not corruption of the bak file.

    Wednesday, December 03, 2014 4:07 PM
  • Hello,

    I am guessing this has to do with the network copy using buffers and the vmdk being moved around. Try using something such as xcopy with /J and trying again.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    • Marked as answer by rferreira.dba Thursday, December 04, 2014 2:55 PM
    Wednesday, December 03, 2014 4:11 PM
    Answerer
  • Original server that generated the .bak file:

    Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) 
    Jun 11 2012 16:41:53 
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

    Server where I CAN restore the .bak file from the network location (not inside the sandbox):

    Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
    Dec 28 2012 20:23:12 
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

    Server inside the sandbox where I get the error when attempting to restore the backup:

    Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64)   Jun 11 2012 16:41:53   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor) 

    rferreira

    Wednesday, December 03, 2014 4:16 PM
  • That is likely a bug in SQL 2008 R2 SP1.  Please install the latest Service Pack on both servers and try again.

    http://support.microsoft.com/kb/321185

    Wednesday, December 03, 2014 6:13 PM
  • Tom, what makes you say that? Is there a specific bug you are aware of in SP1 that causes the behavior I described? Is there a specific KB article that describes the error as you indicate, instead of the one you provided describing how to get the version of the server? TIA, Raphael

    rferreira

    Wednesday, December 03, 2014 10:32 PM
  • Hi Raphael

    A couple of areas to investigate:

    1) I assume that there is sufficient space on the sandboxed server to restore the data?

    2) I assume you are using WITH MOVE to set the destination location of each file in the backup?

    3) Scan the destination drives for any disk corruption on that side - as you state, the backup can be restored elsewhere so maybe it is the destination drives that have the problem?

    Thursday, December 04, 2014 1:27 AM
  • Hi Raphael,

    Another direction worth checking:

    Are you using a compressed backup?

    I had a case with a customer once, where a compressed backup had become corrupted whenever it was copied over the network, while an uncompressed backup hadn't.

    --------------------------------------------
    Guy Glantser
    SQL Server Consultant & Instructor
    Madeira - SQL Server Services
    http://www.madeirasql.com

    Thursday, December 04, 2014 2:03 AM
  • You are running an unsupported version of SQL 2008 R2.  There have been many, many, many changes since SP1.  MS is horrible at publicly documenting problems which are fixed by SP and patches.  Just because it is not listed, does not mean it is not fixed. 

    I found other people reporting the same problem in 2008 R2 SP1, but no solutions of any merit.   

    Before spending any more time on this I would highly recommend updating to the currently supported SP and retesting.  

    You might try updating the "sandbox" version first.  Since the restore to SQL 2012 works. It is possible the problem is with the restore only and not with the bak file creation.

    Thursday, December 04, 2014 2:28 AM
  • Cairney_M,

    1) Yes

    2) Yes

    3) No, no corrupted disk on destination.

    Solution was using copy with the /j option as suggested by Sean. Tks. Raphael


    rferreira

    Thursday, December 04, 2014 2:57 PM
  • Guy, yes, it is compressed. But it should not matter. Compressed should work. Solution was using copy with the /j option as suggested by Sean. Tks. Raphael

    rferreira

    Thursday, December 04, 2014 2:59 PM
  • Tom, thank you for trying to help. I am allergic to "just upgrade" responses though. SPs themselves are not free of bugs and risks. I'd rather have a concrete reason/cause of problem before I blindly go applying SPs. Again, thank you anyways for trying to help. The solution was using copy with the /j option as suggested by Sean. Tks. Raphael

    rferreira

    Thursday, December 04, 2014 3:01 PM
  • That did it. Thanks much buddy. All set here. You saved my day.

    Raphael


    rferreira

    Thursday, December 04, 2014 3:02 PM
  • HI Sean,

    I am running into same problem as mentioned above but with logshipping.

    Both servers are in same domain. Set-up completes successfully.

    Copy job works as expected. but the restore job fails to restore the second file saying ' could not apply  log, data invalid' .

    when running restore filelistonly on the logfile in issue it gives below error

    Cmd:

    RESTORE verifyonly
    FROM disk='G:\xxxx_Copy\xxx_20150604010501.trn'

    Error:

    Msg 3203, Level 16, State 1, Line 2
    Read on "G:\xxxx\xxx_20150604010501.trn" failed: 13(The data is invalid.)
    Msg 3013, Level 16, State 1, Line 2
    VERIFY DATABASE is terminating abnormally.

    and when i run restore filelistonly using netwok path then is seem s good and i can restore the logs as well

    CMD:

    RESTORE verifyonly
    from disk='\\1.2.3.4\xxx_Backups\xxx_20150604010501.trn'

    Output:

    The backup set on file 1 is valid.

    I can assure there is no corruption on the log back up, no lsn mismatch, no log files missing.

    Same version and edition on both servers.

    i am now loosing my patience for setting up logshipping again again when it fails since i have no clue how to resolve this issue.

    Please suggest or comment

    MSM

    Thursday, June 04, 2015 9:54 AM