Slow restore from compressed backups

[mikecare]

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?

[Matthew Flatt]

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
Test Engineer, HyperBac

[mikecare]

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)

[Matthew Flatt]

Hi Mikecare,

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

Thanks,
_________________
Matthew Flatt
Test Engineer, HyperBac

[mikecare]

Yes, they were about 53 MG and 80 MG. Seemed nice and small!

[mikecare]

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.

[Matthew Flatt]

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
Test Engineer, HyperBac

[mikecare]

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

[Matthew Flatt]

If you can email support@red-gate.com and reference this forum post that would be great.

Thanks,
_________________
Matthew Flatt
Test Engineer, HyperBac