Slow Start for Restores
wayneph
Posts: 29
I'm currently using the GUI remotely to test the backup and restore of databases. (Evaluating the product using the trial edition) The backups are running great. I have a 16GB Database backing up to 5.1 GB in under 2 minutes, and a 100 GB Database backing up to 20.1GB in about 36 minutes. Both of these are VAST improvements over the Native SQL option.
However, when I am trying to do the restores, I'm not getting the same type of performance. On the smaller database, I started the restore from the GUI, and it took about 4 minutes before it showed as In Progress. Then it took about 2-3 minutes to run. If I take off the start up time, the run wasn't bad, it just took a while to start. Similarly on the large DB restore (which is still running) it took almost 50 minutes before it appeared as In progress. Now about 1/2 an hour later it's at about 45%.
Is there a setting somewhere that will allow me to make sure the restores start right away? Or am I doing something wrong in my backups that are causing the restores to run slower? Also, I'm using multiple threads to speed up the backups, is there a similar option for the restores?
Thanks for any ideas or experiences that may be floating out there.
However, when I am trying to do the restores, I'm not getting the same type of performance. On the smaller database, I started the restore from the GUI, and it took about 4 minutes before it showed as In Progress. Then it took about 2-3 minutes to run. If I take off the start up time, the run wasn't bad, it just took a while to start. Similarly on the large DB restore (which is still running) it took almost 50 minutes before it appeared as In progress. Now about 1/2 an hour later it's at about 45%.
Is there a setting somewhere that will allow me to make sure the restores start right away? Or am I doing something wrong in my backups that are causing the restores to run slower? Also, I'm using multiple threads to speed up the backups, is there a similar option for the restores?
Thanks for any ideas or experiences that may be floating out there.
--wayne
Comments
If you are restoring over the top of an existing database, this overhead will be minimal (potentially seconds); if you are creating a new file from scratch, this can take a lot of time - and quite easily run into minutes or hours as you have realised.
We are looking into ways to improve this in a future release, so that it informs you that this pre-allocation is occurring, but it is difficult to implement given that SQL Server provides no information as to the progress it has made doing this.
As for your other question, there are no additional "threading" related options for restores, it will use the settings that were provided during the backup.
Hope that helps,
Jason