Slow restore from compressed backups

mikecaremikecare Posts: 19 Silver 1
edited August 10, 2012 10:29AM in SQL Virtual Restore 2
Love the functionality, however my restores seem to take 4x as long. I am using native compressed Prod backups from 64bit Enterprise Edition. I then copy them over to my Dev region and restore. For native Dev Edition restores a 126 GB db/ 27 GB backup takes about 8 min to restore, when using Virtual restore, it took 57 min to restore. For a similar size db it took about 45 min up from a 10 min native restore.

Is this normal for native compressed backups?
· Share on Twitter

Comments

  • Matthew FlattMatthew Flatt Posts: 255 Bronze 5
    Hi Mikecare,

    This shouldn't be normal behaviour, are the SQL Server versions between the two machines the same or different?

    Also when completed, have big are the vmdf/vmdf/vldf files in explorer?

    Thanks,
    Matthew Flatt
    Redgate Foundry
    · Share on Twitter
  • mikecaremikecare Posts: 19 Silver 1
    They are somewhat different

    Prod version: Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) Sep 16 2010 19:43:16 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)

    Dev version: Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (Intel X86) Sep 22 2011 00:28:06 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)
    · Share on Twitter
  • Matthew FlattMatthew Flatt Posts: 255 Bronze 5
    Hi Mikecare,

    Did you notice the size of the vmdf/vndf/vldf file sizes at the end of the restore?

    Thanks,
    Matthew Flatt
    Redgate Foundry
    · Share on Twitter
  • mikecaremikecare Posts: 19 Silver 1
    Yes, they were about 53 MG and 80 MG. Seemed nice and small!
    · Share on Twitter
  • mikecaremikecare Posts: 19 Silver 1
    Do I need to run a restore again to get more info? Not sure if this is standard or if I can do something different to gain the standard restore script time.
    · Share on Twitter
  • Matthew FlattMatthew Flatt Posts: 255 Bronze 5
    Hi mikecare,

    Sorry for the delay, I have been trying to reproduce your issue here but unfortunately with no success. Are you using version 5.7 of the product?

    Would you be able to run both the native and SVR restores again with some extra SQL Server logging turned on for comparison?

    Execute the following against the master db:

    DBCC TRACEON (3004, 3605, -1)

    Run a native restore followed by an SVR restore.

    Turn off the extra logging:

    DBCC TRACEOFF (3004, 3605, -1)

    I would like to know if there is a specific part of the restore process which is taking the extra time, such as the recovery phase.

    Thanks,
    Matthew Flatt
    Redgate Foundry
    · Share on Twitter
  • mikecaremikecare Posts: 19 Silver 1
    I am running 5.7. I was able to run as you requested. What is the best way to get you the file or data? The backup ran in about 14 min for the native restore vs 45 min for the virtual
    · Share on Twitter
  • Matthew FlattMatthew Flatt Posts: 255 Bronze 5
    If you can email support@red-gate.com and reference this forum post that would be great.

    Thanks,
    Matthew Flatt
    Redgate Foundry
    · Share on Twitter
Sign In or Register to comment.