Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 Jan 17 '12 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 Jan 17 '12 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 Jan 17 '12 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 Jan 17 '12 at 18:06
add comment

2 Answers 2

up vote 6 down vote accepted

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.

share|improve this answer
1  
Oh, good point. I just noticed the MDF file is ~2 gigs, but the LDF file is 67 gigs –  O.O Jan 17 '12 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 Jan 17 '12 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 Jan 17 '12 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 Jan 17 '12 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 Jan 17 '12 at 18:24
show 3 more comments

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

share|improve this answer
    
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 Jan 17 '12 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 Jan 17 '12 at 17:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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