Posted by Erland Sommarskog on 11/09/07 22:13

(codefragment@googlemail.com) writes:
> What would be the quickest way to create a backup and revert program
> on an sql (2000) database?
>
> - Can you create a transaction on a database, regardless of the
> connections and then
> rollback it all via an external program
> - Could you monitor the changes with profiler and then reverse those?
> - If desperate, could you backup the db from a tool and then restore
> it? (too slow to be practical?)
>
> Not sure how to do this so any offers would be appreciated

BACKUP/RESTORE sounds like the best bet to me. At least the safest. As for
speed, that depends on the database size. If the database is only a few
gigabytes, it should not be an issue. If the database is several TB,
RESTORE would be painful.

A log-reader program could be alternative. They are able to read the
transaction log and undo statements they find in the log. There are
several vendors with such products: Lumigent, LOG PI, Red Gate.

Profiler? I don't know of any products that work from trace. (Not Profiler,
as Profiler can lose events.)

But the real question is what you really want to achieve and why you want
to do it on SQL 2000. This sounds like a perfect scenario for database
snapshots, a new feature in SQL 2005.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]