any way to increase speed of restore ?

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

any way to increase speed of restore ?

Postby AlexG » Fri Oct 19, 2012 3:13 pm

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 ?
AlexG
 
Posts: 14
Joined: Fri Apr 07, 2006 7:07 pm

Postby petey » Mon Oct 22, 2012 12:05 pm

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
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm

Postby AlexG » Mon Oct 22, 2012 2:27 pm

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
 
Posts: 14
Joined: Fri Apr 07, 2006 7:07 pm

Postby petey » Mon Oct 22, 2012 3:18 pm

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
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm

Postby AlexG » Mon Oct 22, 2012 3:22 pm

100 Mbit/s (megabit per second)
AlexG
 
Posts: 14
Joined: Fri Apr 07, 2006 7:07 pm

Postby petey » Mon Oct 22, 2012 3:44 pm

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
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm

Postby AlexG » Mon Oct 22, 2012 4:14 pm

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
 
Posts: 14
Joined: Fri Apr 07, 2006 7:07 pm

Postby petey » Mon Oct 22, 2012 4:24 pm

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
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm


Return to SQL Backup 6

Who is online

Users browsing this forum: No registered users and 0 guests

cron