any way to increase speed of restore ?

AlexGAlexG Posts: 24
edited October 22, 2012 11:24AM in SQL Backup Previous Versions
Hi,
we have 230 GB db on physical server - Server_A (24 CPU, 32 GB RAM)
I use THREADCOUNT = 32 , COMPRESSION = 3 , it takes only 11 min backup db to SUN drive - server_S

for data mirroring I need to restore this db on another server (250 km away :) Server_B

Currently I run restore on server_B (not a physical server

EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [Prices ] FROM DISK = ''\\server_S\server_A_Prices.sqb'' WITH NORECOVERY, DISCONNECT_EXISTING, REPLACE"'


restore time is 6,5 hrs

is any way to speed up restore process by changing options in
master..sqlbackup '-SQL "RESTORE DATABASE ?
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    Could you please post the contents of the log file for the restore process? The default folder where the logs are stored is C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\<instance name> on Windows 2003 and older, and C:\ProgramData\Red Gate\SQL Backup\Log\<instance name> on Windows Vista and newer.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • AlexGAlexG Posts: 24
    SQL Backup log file 6.5.1.9

    -SQL "RESTORE DATABASE [Prices] FROM DISK = '\\server_S\server_A_Prices.sqb' WITH NORECOVERY, DISCONNECT_EXISTING, REPLACE "
    ERRORS AND WARNINGS


    18/10/2012 8:53:32 AM: Restoring Prices (database) on INFOSYSREP instance from:
    18/10/2012 8:53:32 AM: \\server_S\server_A_Prices.sqb

    18/10/2012 8:53:33 AM: RESTORE DATABASE [Prices] FROM VIRTUAL_DEVICE = 'SQLBACKUP_ED741013-A3AC-49B6-A0B2-30D9A1A102AF' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576 , NORECOVERY, REPLACE

    18/10/2012 3:25:26 PM: Warning 204: Thread 0 warning: ReadFile failed for file: \\server_S\server_A_Prices.sqb at position: 15297677312
    18/10/2012 10:44:08 AM: Re-attempt: 1
    18/10/2012 10:45:09 AM: OpenTargetFile.CreateFile error: The specified network name is no longer available.

    18/10/2012 10:45:39 AM: Re-attempt: 2
    18/10/2012 10:45:41 AM: File (\\server_S\server_A_Prices.sqb) reopened and read succeeded (524288 bytes) at position (15297677312).
    18/10/2012 3:25:26 PM:
    18/10/2012 3:26:16 PM: Processed 23397736 pages for database 'Prices', file 'Prices_Data' on file 1.
    18/10/2012 3:26:16 PM: Processed 0 pages for database 'Prices', file 'Prices_Log' on file 1.
    18/10/2012 3:26:16 PM: Processed 70 pages for database 'Prices', file 'Prices_LOG2' on file 1.
    18/10/2012 3:26:16 PM: Processed 6423 pages for database 'Prices', file 'sysft_ClientSearch' on file 1.
    18/10/2012 3:26:16 PM: RESTORE DATABASE successfully processed 23404228 pages in 23490.054 seconds (8.162 MB/sec).
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    8.162 MB/sec is about 68.5 Mbps. How fast is your network connection from server B to server S?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • AlexGAlexG Posts: 24
    100 Mbit/s (megabit per second)
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    You could try splitting the backup into 2 files, which SQL Backup could then process in parallel. However, 100 Mbps is only about 11.92 Mbps. Thus, even if SQL Backup could pull data at that maximum rate, it'll still take over 4 hours to restore the database.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • AlexGAlexG Posts: 24
    is parelel restore running by default while restoring from multiple backup files (I don't see option in restore command)?

    does option THREADPRIORITY will make any affect if the is no other restore running at the same time ?
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    If you split a backup, say into 2 files, then during the restore, you would also need to provide the name of both files, and SQL Backup will process both files in parallel.

    THREADPRIORITY wouldn't help, since the restore process is I/O bound, not CPU-bound. Even at a 100 Mbps transfer rate, it'll still take approximately 4 hours 45 minutes to restore the database.

    If your secondary database can be used in read-only mode, have you considered log shipping instead, or using differential backups to update your secondary database (assuming differential backups are significantly smaller than full backups)?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.