Database Restore Takes Hours - Is this normal?

Question

Database Restore Takes Hours - Is this normal?

dharris-623527

Mr or Mrs. 500

Points: 544
More actions
December 15, 2008 at 7:06 am

#202896

Hi,
I have a database which is over 30GB in size. The MDF file is 12GB and the LDF file is 19GB. When I login into SQL Manager and do a backup, it takes about 30 minutes and the resulting file is around 11GB.
When I goto restore this backup file into a database it takes over 2 hours. Just wondering is this normal? And is there a way to speed this up. The server SQL is residing on is Quad-Core Xeon with Sata Disks in Raid 1.
Any input would be appreciated.
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Might be normal (not sure).

Try putting the backup file on a different disk set than the drives where you restore to and see how much faster it is...

I'd expect the restore to take longer than the backup... but 4 to 1 seems like a lot to me.

Lowell SSC Guru Points: 323518 More actions · Answer 2

I'd look for two things: how much free disk space is on the server? if it's not much over the size of the restore, that would be an issue, as the restore process tries to reserve that space in as big of a continuous blocks of disk space as possible.

the other thing is Raid 1(mirrored) ; that means it taked two writes (one to each disk), where it would normally be just a single write....that's expected for RAID1, but that compromise for data integerity is slowing you down a bit of course.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Kyle Neier , SSCrazy Points: 2252 More actions · Answer 3

When you are taking the full backup, SQL is only writing 11GB.

When you go to restore, SQL has to zero out the entire file before it can start to populate it with data*. In your case, SQL has to lay down 12GB+19GB (31GB) before it can get started with the 11GB restore.

In essence, the backup process is writing 11GB taking 30 minutes. The restore is writing 42GB taking 120 minutes. The ratios seem appropriate to me.

*I'm presuming you have not enabled instant file initialization.

Atul DBA Hall of Fame Points: 3610 More actions · Answer 4

But even restoring the 42GB file should not take more then one hour. I things something else is going on the server or the disk is not having more than 10% free space apart from 42GB required for restoration.

The less disc space may also cause the slow restoration process.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 5

Low disk space will slow it, but I'm not sure it should be hours. Is the server overloaded?

Kyle Neier , SSCrazy Points: 2252 More actions · Answer 6

I was so focused on the ratio I skipped over the raw speed.

I still think the backup and restore are operating consistently, but 6MB/sec on a SAS mirror is slower than what I would expect.

The restore could be sped up by implementing instant file initialization (be wary of any security concerns) and by shrinking the .ldf file. Only shrink the ldf if you're certain it was grown by an anomalous event or lack of appropriate maintenance. If that space is needed for operation, leave it alone.

Although these modifications may work, as others have pointed out, you may be masking the underlying problem.

As a curiosity, I've not seen drive free space be an issue if the free space is contiguous. If the drive is low on space and that space is heavily fragmented, I've seen that slow things down. Do any of you have examples of lack of free space being the only contributing factor to IO slowness?

Raja.V SSC Veteran Points: 288 More actions · Answer 7

Check if the servers i over loaded. It sometimes usually takes that long....and sometimes it completes with in less time. So we can't actually speculate the exact time it takes to restore a database backup.

dharris-623527 Mr or Mrs. 500 Points: 544 More actions · Answer 8

Hey guys,

Sorry for the delay. The server has a Tomcat webserver on it and hosting only one application, very min. resources. I checked disk space, there 400GB still free. Now I have another server with the same setup. I tried it on that one, and it takes around 30 minutes or so. Both SQL version were suppose to have SP2 installed. However on the server that is slow its showing this as the version 2005.90.1399.

So I'll try installing SP2 again and see if that helps.