all posts

Restoring large SQL Server databases

Published to Blog on 27 Jun 2007

When restoring a large SQL Server database (double-digit GBs) insure that the backup file is not on the same physical drive that your data file will reside. Having them on the same drive seems to slow down the restore process 10 fold. That number is pulled from thin air based on visual-only observation - YMMV.

Today marks the third time I have learned that lesson. The first time I thought I must have just been doing something wrong or one of the drives must have been much slower than it should have. The second time was in conjunction with Rob Baugh and he confirmed it. Today I had no choice but to do them from the same drive because I only had one drive with enough room to hold the backup file or the data file.

NOTE: Interesting that today on my PC, using a standard 7200RPM SATA drive, it seemed to perform fine. However, in two previous attempts on brand new servers with fast SCSI drives it proved to be much, much slower when reading from and writing to the same drive. Perhaps it is because of the difference in SATA/SCSI? Now I am really perplexed. Perhaps someone with much better hardware chops than I will come along and provide an explanation.

A few details - approx 13GB database and backup file.
from one SCSI drive to another: approx 10 minutes
from one SCSI drive to the same: looked like it was going to take an hour - after 10 minutes it was only 10%
from one SATA drive to the same: approx 10 minutes

Dan Hounshell
Web geek, nerd, amateur maker. Likes: apis, node, motorcycles, sports, chickens, watches, food, Nashville, Savannah, Cincinnati and family.
Dan Hounshell on Twitter