Delay in Restoring from SQL Backup

APT-1

We’ve recently installed SQL Backup Pro 4.6.0.815 onto a pair of identical 32-bit Windows 2003 servers, each with 32GB RAM. Each server is running SQL Server 2005 Enterprise Edition (SP2) with the AWE switch enabled. Each server also has dual quad-core Xeon processors. We have a 320GB database which is updated each night on server “A” and then backed-up across the network to Server “B”. Each day’s backup is multithreaded to produce 8 files with no encryption and the whole process typically takes around 18-20 minutes. This image is then restored each day onto server “B” before being presented for read-only reporting to the users. The restore is configured to overwrite a fresh, empty default 3MB database.

My question concerns the restore process. Yesterday’s restore of this database took a total of four hours to complete, which is not atypical. I notice that when restoring a database using the GUI, the Restore dialog box initially shows “Restoring…” in the top left-hand corner, with elapsed time in the top right-hand corner. After some time, the first message will change to “Restored…XXX GB” and continues incrementing all the way through to completion. Today I noticed that it took 1 hour and 12 minutes before the restore progress message began to change. What is the cause of this substantial time lag? Is the latency due to SQL Backup or SQL Server? What can be done, if anything, to minimise this inherent delay? Finally, is there any way to restore the database (MDF and LDF) files themselves and only then attach the recovered database to SQL Server?

Scott

Eddie D

Hi

Thank you for your post into the forum.

I am aware of occasions of the restore process taking longer than expected to complete.

Also the time taken from the restore task being actually triggered to when it actually starts the restore period taking longer than expected. This is due to the time taken creating the files sizes of the correct size.

The first place to confirm any problems would be to view the SQL Backup Log file on the server performing the Restore Task. This Log file is located by default in the following directory:

C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\(LOCAL) or SQL Instance Name

I will send you a private message if you wish me to take a look at the SQL backup Log file.

Also what compression level was used to create the backup on the source server?

Many Thanks
Eddie

Eddie Davis
Red Gate Software Ltd
Technical Support Engineer
E-Mail: support@red-gate.com