I take it that you create a clone by means other than backup/restore
and that you create a snapshot using EMC hardware or the like.

SQL Server won't let you restore transaction logs unless you've first
restored the database, or at least a database file group or file. Then
you have to restore all of the transaction log backups, in order, from
the first log backup taken after the database backup up to the latest
you're interested in. If you want to restore up to the latest moment
possible you must back up the current transaction log before you start
the restores.

SQL Server keeps track of all of the backups (in the msdb database) and
won't let you do things out of sequence. The first trick is to get SQL
Server to think you've made a database backup when you've actually made
a clone or snapshot. The second trick is to make the clone or snapshot.
SQL Server has an exclusive lock on the database and log files. If you
are using hardware assistance (EMC, etc.) you can use the hardware to
bypass SQL Server.

SQL Server 2000 has support to help do the two tricks (SQL VDI Snapshot
support). It is also possible to do them with SQL Server 7.0 but you
need help from Microsoft. Writes are disabled during the backup snapshot
but the database is still in service and usable. In either case you
would still do frequent log backups the normal way.

There are some alternatives. You can detach the database, copy all of
the database and log files, and then reattach the database. To restore
you copy the database and log files back and reattach the database. But
this requires taking the database out of service while it is being
detached and copied. See sp_detach_db and sp_attach_db in SQL Server
Books Online.

What Fred Johnson mentioned can be achieved using log shipping to a
standby SQL Server, using a fail over cluster, or using Data
Transformation Services (DTS) or replication. (The problem of not being
able to do a log backup during a full backup is solved in SQL 2000. SQL
2000 also provides new support for log shipping and improved support for
fail over clustering.)

I don't think SQL Server has anything similar to the Oracle archiving
that Alex Sokolek mentioned.

If you have the hardware you are probably better off doing a snapshot
every hour. If you do that you can use the BA client to occasionally
backup the snapshot and restore a backed up snapshot. Once the restored
snapshot is available to SQL Server you may have to do an sp_attach_db
to synchronize SQL Server with the restored database.

"Kelly J. Lipp" <lipp AT storsol DOT com> wrote:
> Basic question:
>
> 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?

--
Bob King     IBM Global Services
Bob King     IBM Global Services
bobking AT us.ibm DOT com