There’s really not much written (that I’ve found, please add comments with links to posts you’ve found) that talks about tuning database restores.
I started messing with some restore parameters last week and found that it’s possible to make some dramatic performance improvements.
How this started
We were performing a data center migration at work. As a part of this migration we were going from SQL 2000 to SQL 2008R2 for some of our databases.
The database, we’ll call MyDB, is 620GB in size. There are 5 data files and a single log file. Current backups are performed using Snap technology, which wasn’t going to help us with performing a migration as we needed to get the data over and setup log shipping, so we needed to perform a regular backup.
Third party tools were not an option as this SQL instance was running SP3a and we weren’t able to find anything that would work below SP4.
A full backup was taken to local disk, it took a little over 9 hours (server and disk limitations prevented any work to try and improve that).
The backup file was transferred to a USB drive, taken to the new datacenter and copied over to a CIFS share where it would be available for performing restores.
Hardware/software setup
It’s going to help to have an idea of the configuration that’s being used here.
Continue reading on SirSQL.net.