Red Gate forums :: View topic - any way to increase speed of restore ?
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 6
SQL Backup 6 forum

any way to increase speed of restore ?

Search in SQL Backup 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
AlexG



Joined: 07 Apr 2006
Posts: 14

PostPosted: Fri Oct 19, 2012 3:13 pm    Post subject: any way to increase speed of restore ? Reply with quote

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 Smile 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 ?
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2305

PostPosted: Mon Oct 22, 2012 12:05 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
AlexG



Joined: 07 Apr 2006
Posts: 14

PostPosted: Mon Oct 22, 2012 2:27 pm    Post subject: Reply with quote

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).
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2305

PostPosted: Mon Oct 22, 2012 3:18 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
AlexG



Joined: 07 Apr 2006
Posts: 14

PostPosted: Mon Oct 22, 2012 3:22 pm    Post subject: Reply with quote

100 Mbit/s (megabit per second)
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2305

PostPosted: Mon Oct 22, 2012 3:44 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
AlexG



Joined: 07 Apr 2006
Posts: 14

PostPosted: Mon Oct 22, 2012 4:14 pm    Post subject: Reply with quote

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 ?
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2305

PostPosted: Mon Oct 22, 2012 4:24 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group