any way to increase speed of restore ?

• 
  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.

  petey October 22, 2012 07:05

  - 0 +
• 
  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).

  AlexG October 22, 2012 09:27

  - 0 +
• 
  8.162 MB/sec is about 68.5 Mbps. How fast is your network connection from server B to server S?

  petey October 22, 2012 10:18

  - 0 +
• 
  100 Mbit/s (megabit per second)

  AlexG October 22, 2012 10:22

  - 0 +
• 
  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.

  petey October 22, 2012 10:44

  - 0 +
• 
  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 ?

  AlexG October 22, 2012 11:14

  - 0 +
• 
  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)?

  petey October 22, 2012 11:24

  - 0 +

Add comment

Please sign in to leave a comment.