6

Although, the database backup file is only 2GB, the restore can take up to 15-20 minutes even on a x64 12G RAM, i7 machine. When I backup similar sized backup files made from a different database, the restore finishes in 3 seconds. The backup file contains only one backup set (I always check overwrite existing data option). On other slower machines it takes even longer to restore.

What could be causing the restore to take so long?

Improve this question
4
  • Are you restoring those other similarly sized databases from the same disk? What happens if you take a new backup of the problem database and restore that immediately?
    – Nick Chammas
    Commented Jan 17, 2012 at 17:11
  • @NickChammas - I don't understand your first question. As to your second question, it is consistent no matter when the backup is done/restored.
    – O.O
    Commented Jan 17, 2012 at 17:19
  • You mentioned that restores of other databases took a few seconds. I want to make sure that the backups you are restoring are all on the same disk to eliminate the disk as the cause of the one slow restore.
    – Nick Chammas
    Commented Jan 17, 2012 at 17:47
  • @NickChammas - Yes, they are on the same disk. It happens on other machines that restore the same backup file as well.
    – O.O
    Commented Jan 17, 2012 at 18:06

2 Answers 2

Reset to default
7

The backup size is simply used 8k pages. These 8k pages are part of the MDF. On restore, the MDF and LDF files may need recreated (if already existing and different in some way) or just created

So backup file size isn't an indicator of space used on disk by the restored database. And this ignores backup compression too

So, if the MDF is 100GB (with 2GB actually used), : unless you have Instant File Initialisation on, then the 100GB needs zeroed. Note the LDF must be zeroed.

Improve this answer
8
  • 1
    Oh, good point. I just noticed the MDF file is ~2 gigs, but the LDF file is 67 gigs
    – O.O
    Commented Jan 17, 2012 at 17:21
  • Can you explain what you mean by the LDF must be zeroed? Will this make the LDF smaller than 67 gigs?
    – O.O
    Commented Jan 17, 2012 at 17:23
  • @subt13: the file on disk must be written with zeroes. See the link I posted on IFI please: "Data and log files are first initialized by filling the files with zeros..."
    – gbn
    Commented Jan 17, 2012 at 17:35
  • Yes, that looks to reduce the time it would take to restore. One more thing, we don't care about the LDF file, does that mean we should also switch to simple recovery model?
    – O.O
    Commented Jan 17, 2012 at 17:40
  • 1
    You could switch to SIMPLE mode and the log wouldn't keep any bigger but in order to reduce the restore time you would still need to shrink the log file down to a more reasonable size. simple-talk.com/sql/database-administration/…
    – Jason Cumberland
    Commented Jan 17, 2012 at 18:24
1

I'd be curious to see what recovery model you are using on the database in question. On a full recovery model, both the database and log are being backed up - on a simple, just the database. Your other database may restore quicker because it is on a simple recovery model.

See: http://msdn.microsoft.com/en-us/library/ms189275.aspx

Improve this answer
2
  • Ya, the log file is 67Gs and our product has no need of a log file. If the restore is taking so long because of the log file, then are you suggesting we change to a simple recovery model?
    – O.O
    Commented Jan 17, 2012 at 17:35
  • 1
    If your database is not highly transactional (COMMITS and ROLLBACKS), then it might be a good idea. Depends on how you use the DB. For example, my SSRS databases are on simple, but my SSIS databases are on full.
    – OliverAsmus
    Commented Jan 17, 2012 at 17:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.