Restore backup to USB 2.0 - Performance?

Posted by James Palmer on 15-Dec-2014 02:31

I have a copy of our databases on a USB 2.0 drive that I use as a development environment. Our main database is ~250GB and it takes a good few hours to restore. Does anyone have any tricks that I could use to improve that time? I've requested a USB 3.0 drive but not had the request approved as yet. I'm not too bothered about integrity of the data as it's just for my personal use. 

All Replies

Posted by gus on 22-Dec-2014 14:14

reformat the drive. "but then i'll lose all my existing other files !" you say. ok then. get another drive you can dedicate to just the database and format first, then restore.

Posted by Rob Fitzpatrick on 22-Dec-2014 17:33

How do you copy the DB to the drive?  Windows Explorer?  Give rsync a try.  If you're copying the same database to the drive on a regular basis, even if it isn't identical as it changes day to day, you may find that rsync works better than OS file copying.

Posted by James Palmer on 23-Dec-2014 03:06

I use a prorest to get the files onto the drive. If the files themselves are on the drive then the latency of USB2 means the prorest takes about 2 years to complete (may be a slight exaggeration). I use a prorest primarily because it has the side-effect of proving the backups are working meaning I don't have to do that as a separate action.

Thanks for the idea [mention:9617a07f61934bc98f6fccb3b7fabfae:e9ed411860ed4f2ba0265705b8793d05]. As it happens I basically just have databases on my drive as it is. I moved my workspace etc onto Dropbox a while back. The other databases are small or I can change them to just schema holders, and thus they can live in the cloud too. So I might give the reformat option a go.

Posted by ke@iap.de on 23-Dec-2014 04:43

USB 2.0 is about 20 MB / sec, most times less. Physics will win...

a) Use one full back up, save that at home. Then use differential the next times, until restoring is annoying. Then again use full bkup.

b) If you compress outside the USB drive, storing and retriving is much faster. But compressing/decompressing takes also time. If you can do it parallel with multi CPU, that should be faster then single copy.

c) Time, effort and simplicity for this is like quality, price and end date for programming - to get everything as you like is mostly not possible ;)

wish all a good time at Christmas!

Klaus

Posted by James Palmer on 23-Dec-2014 04:48

The simplest solution would be a USB3 drive but unfortunately I've not managed to successfully justify that yet! :)

Posted by Rob Fitzpatrick on 23-Dec-2014 04:52

Time is money.  You're well on your way to justifying the simple solution by trying to find a different one. :)

Posted by James Palmer on 23-Dec-2014 04:55

Preaching to the converted. I use my dev DB for testing DBA activities, and a binary D&L of any significance over USB2 is not pretty. But yet I can't convince the powers that be of the necessity for a better solution.

Posted by Rob Fitzpatrick on 23-Dec-2014 05:01

Management won't be convinced until they are replaced by reasonable people (rather unlikely) or you document for them (a) how much money they are losing because you are waiting for disk I/O instead of doing productive work, (b) the cost of a solution for that problem, and (c) the ROI of that solution.

This thread is closed