ADSM-L

Re: SQL backup and restore using TDP

2000-08-29 14:46:46
Subject: Re: SQL backup and restore using TDP
From: Alexander Z Sokolek Jr <asokolek AT KEYSPANENERGY DOT COM>
Date: Tue, 29 Aug 2000 14:46:28 -0400
Not answering as an ADSM guy, but addressing the philosophy involved in
protecting a large database...

150 to 500gb?  Once an hour?  That seems overwhelming, unless I'm
missing something...

We tend to do a two tiered system.  We periodically backup everything,
say once per week, with transactions locked out.  We then turn on
"archiving" (an Oracle concept, but it might apply to MS SQL Server)
which causes archive logs (persistent copies of the redo logs prior to
scratch) to get written to sequential files.  Backup the archive logs
more often, say once per day.  You could delete the archive logs if
desired after verifying that they have been backed up.

A failure occurs...

You use the normal DBMS facilities to attempt recovery.  Depending on
the OS and the DBMS, and their built-in protections, you may only need
to just restart.  The most recent redo logs still exist on disc and the
DBMS should be able to roll back to the last committed transaction.  For
recovery, you restore from the newest full backup, including all archive
logs since then, and you roll forward to the point of the last backed up
archive log.  That should dovetail with the archive logs that are still
on disc, and you should be able to continue rolling forward to the last
valid archive log, bringing your restore to within minutes of the point
of failure.

In practice, we have been able to do semi-major restores with only an
hour's downtime, without losing more than a few minutes of
transactions.  Think about this.  Talk to your DBA's - it is essential
that backup be integrated into your DBMS strategy.  Good luck.

Alex Sokolek

"Kelly J. Lipp" wrote:
>
> Can I clone or snapshot an MS SQL 7 database and then take hourly
> "incremental" backups and do the following restore:
>
> restore the clone (either from tape or from the clone itself) and
> apply the "incremental" restores?
>
> I'm after a rather quick restore of a large database.  Perhaps
> between 150 and 500 GB.  I thought to use TDP or simply capture the
> logs by cloning or snapshot or via the ba client.
>
> Would I be better off simply snapshotting once per hour and leaving
> TSM out of the picture?  Perhaps backing up the snapshot periodically
> for DR purposes?  This customer wants to be able to get to a previous
> state of the database but not go back 24 hours.  Loss of an hour's
> worth of data is acceptable, but not 24.  What are others doing to
> meet this sort of a requirement?
<Prev in Thread] Current Thread [Next in Thread>