Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Restore over network going 12x slower

Author  Topic 

Beacher
Starting Member

3 Posts

Posted - 2011-03-03 : 11:43:35
I know backups over the network are not preferred, and nothing is guaranteed, however my DB restore of 180GB was going at 1.5MB/sec, (SQL Server 2005) yet if I transfer a file (from a windows share) it goes at 30MB/sec (1 GB file), all tests point to 30MB/sec, but as soon as SQL Server restore is involved, it snails along at 1.5MB/sec .. the backup of a database is fast (about 6 hours to backup, yet took 33 hours to restore).

The server storing the backup (backed up to, and restored from) is the same, and both servers (the server backed up, and the server restored to) are blade's, both in the same enclosure, same SAN etc..

Any suggestions on what I can look at that is causing this? Backing up and restoring over the network is the only option I have, the enclosure is USB 1.1, so I can't even back up to a USB drive.

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-03 : 12:31:34
Have you considered using backup compression? For SQL Server 2005, it isn't free but it's relatively cheap if you go with Red Gate's solution. Restores are MUCH faster if the backups are compressed, plus the backups are MUCH faster and the size of them are MUCH smaller.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Beacher
Starting Member

3 Posts

Posted - 2011-03-03 : 15:32:22
I haven't looked at compression, I'll look into it as a solution if I can't figure out this problem, but I don't understand why SQL would be so slow when all other services are flying over the network.

What makes it even more confusing is the backup ran in 1/4 of the time as the restore took of that same backup.

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-03 : 15:42:26
Well restores are slower than backups. You'll need to do a local restore to see how much slower the restore is for that particular database size.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Beacher
Starting Member

3 Posts

Posted - 2011-03-03 : 16:14:22
My problem is it's impossible for me to do a local restore, there is not 180GB available on any disk, as well as I can't connect external storage (only USB 1.1 is available).

I appreciate your suggestions, it looks like I'll look into compression for now, I'll keep poking around, I should think SQL Server doesn't need 28.5MB/sec in overhead for restoring, hopefully I can find some way to mitigate that.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-03 : 16:50:09
quote:
I can't connect external storage (only USB 1.1 is available).
Even if you had USB 2.0, don't restore a database to an attached USB drive. The performance will still suck, and all you need is a bumped cable or drive to possibly corrupt your database.

Is the drive you're restoring to fragmented? That could explain why it's so much slower.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-03 : 17:45:35
Can you possibly get more disk space?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

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

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page
   

- Advertisement -