Time taken for backup and recovery

Bret Halford

2009-12-01 22:55:46 UTC

Post by unknown
Any idea how long would it take to backup or restore a
sybase database running on HP Unix (DB space used approx
600G, unused space 200G).
Whenever I talk to the DBAs to request for backup before
special batch runs (heavy sql) & request for a restore of
database incase the process fails, they respond its not
feasible and say it takes more than 2 days to restore the
database. Is this true ?
Any recommended books/guidelines on backup & recovery to
read.
Thank you
Yog

It depends on a number of factors, such as the backup technique
used, typical size of the active part of the transaction log,
device speeds, number of dump stripes used, whether compression
is used, whether sp_dumpoptimize is used.

There are two components - the time to simply copy in all the
pages and then the time to recover the active part of the log
to get the database to a consistent state. The later can be highly
variable depending on how much is in the transaction log.
However - if the devices have an i/o throughput of, say 8GB/hour,
then a 400 GB database could take 50 hours to write out using a single
stripe. In general it will take longer to restore than to dump.

It is certainly possible to do considerably better than 2 days
with the right hardware and using the QUIESCE DATABASE
and device copy (or mirror breaking) technique - for instance
a matter of minutes or seconds using something like EMC's TIMEFINDER.
http://www.sybase.com/content/1020459/deployingfinal12_12.pdf

I do hope your DBAs are taking some kind of backup on a regular
schedule or have some other way to rebuild the database should
a disaster occur. [I had a customer once whose data center was
struck by lighting twice - the first time frying the primary
bank of hard drives, the second strike took out the warm backup...
fortunately he had database dumps on tape.]

-bret