What are the challenges you face when working across database platforms? Take the survey
Options

Slow restore Performance

ThrallThrall Posts: 6
edited March 13, 2007 11:39AM in SQL Backup Previous Versions
I am having issues with long running restores. I have even restored locally and across the network and both restores are very slow, almost at the speed of the naïve restore of sql server 2000. I tried different compression levels to see if that made a difference but it seemed not to.

Here is the code I am using:


SET @DBRestore = '-SQL "RESTORE DATABASE JSLREDGATE FROM DISK ='

SET @FileLocation = '''c:\(OSCMS-MSLE-DB80)_(MSN_DEV)_20070208_072508_FULL.sqb'' '
SET @Move1 = 'WITH MOVE ''MSN_Data'' TO ''d:\SQL_Data\JSLREDGATE_MSNDEV.mdf'', '
SET @Move2 = 'MOVE ''Secure_Data_01'' TO ''d:\SQL_Data\JSLREDGATE_Secure.ndf'', '
SET @Move3 = 'MOVE ''MSN_Log'' TO ''D:\SQL_Data\JSLREDGATE_MSNDEV_Log.ldf'', RECOVERY"'
SET @Restore = @DBRestore + @FileLocation + @Move1 + @Move2 + @Move3
EXECUTE Master..sqlbackup @Restore




SQL Backup log file
02/08/2007 10:25:06 AM: Restoring JSLREDGATE (database) from:
c:\(OSCMS-MSLE-DB80)_(MSN_DEV)_20070208_072508_FULL.sqb

02/08/2007 10:25:06 AM: RESTORE DATABASE [JSLREDGATE] FROM DISK = 'c:\(OSCMS-MSLE-DB80)_(MSN_DEV)_20070208_072508_FULL.sqb' WITH RECOVERY, MOVE 'MSN_Data' TO 'd:\SQL_Data\JSLREDGATE_MSNDEV.mdf', MOVE 'Secure_Data_01' TO 'd:\SQL_Data\JSLREDGATE_Secure.ndf', MOVE 'MSN_Log' TO 'D:\SQL_Data\JSLREDGATE_MSNDEV_Log.ldf'

Processed 2376240 pages for database 'JSLREDGATE', file 'MSN_Data' on file 1.
Processed 328 pages for database 'JSLREDGATE', file 'Secure_Data_01' on file 1.
Processed 7 pages for database 'JSLREDGATE', file 'MSN_Log' on file 1.
RESTORE DATABASE successfully processed 2376575 pages in 1276.057 seconds (15.257 MB/sec).



SQL Backup log file
02/08/2007 7:48:12 AM: Restoring JSLREDGATE (database) from:
\\OSCMS-MSLE-DB81\SQL_Backup\OSCMS-MSLE-DB80\Msn_Dev\(OSCMS-MSLE-DB80)_(MSN_DEV)_20070208_072508_FULL.sqb

02/08/2007 7:48:12 AM: RESTORE DATABASE [JSLREDGATE] FROM DISK = '\\OSCMS-MSLE-DB81\SQL_Backup\OSCMS-MSLE-DB80\Msn_Dev\(OSCMS-MSLE-DB80)_(MSN_DEV)_20070208_072508_FULL.sqb' WITH RECOVERY, MOVE 'MSN_Data' TO 'D:\SQL_Data\JSLREDGATE_MSNDEV.mdf', MOVE 'Secure_Data_01' TO 'D:\SQL_Data\JSLREDGATE_Secure.ndf', MOVE 'MSN_Log' TO 'D:\SQL_Data\JSLREDGATE_MSNDEV_Log.ldf'

Processed 2376240 pages for database 'JSLREDGATE', file 'MSN_Data' on file 1.
Processed 328 pages for database 'JSLREDGATE', file 'Secure_Data_01' on file 1.
Processed 7 pages for database 'JSLREDGATE', file 'MSN_Log' on file 1.
RESTORE DATABASE successfully processed 2376575 pages in 1575.314 seconds (12.358 MB/sec).

Comments

  • Options
    peteypetey Posts: 2,358 New member
    12 MB/sec to 15 MB/sec is indeed low. If you were to copy a file from the C:\ to the D:\ drive, what is the transfer rate like? Also, are you using an Itanium box?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    The box that I have run the naive restore and redgate restore and litespeed restore on is an old box but the results of redgate restore is slower. It is a pentium III 1.26 GHZ, 2 GB RAM.

    File size:
    4,952,697 KB to be copy from network share c$ to network share d$ took 14min.
  • Options
    peteypetey Posts: 2,358 New member
    What is the restore rate reported by the native restore? Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.