any way to increase speed of restore ?

[AlexG]

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 ?

[petey]

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 7

[AlexG]

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

[petey]

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 7

[AlexG]

100 Mbit/s (megabit per second)

[petey]

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 7

[AlexG]

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 ?

[petey]

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 7