snapshots and reverts

Hi
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

ta

Re: snapshots and reverts

(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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: snapshots and reverts

> But the real question is what you really want to achieve and why you want
> to do it onSQL2000. This sounds like a perfect scenario for database
> snapshots, a new feature inSQL2005.

We use sql 2000 and sql 2005, mainly the former, and our customers are
on sql 2000 so
I tend to that.
That being said what I want is an aid for developers so we could use
sql 2005.
We run through a process to debug some
problem, we then want to revert the database back to the state it was
in so we can repeat that
process to help in debugging. At the moment I can get around this by
making a note of the tables
involved and use deletes, inserts to get the data back to how I want
it but it would be nice if
I could just click 'backup' and and then later revert. If it was fast
it could be a real help.

Re: snapshots and reverts

(codefragment@googlemail.com) writes:
> We run through a process to debug some problem, we then want to revert
> the database back to the state it was in so we can repeat that process
> to help in debugging. At the moment I can get around this by making a
> note of the tables involved and use deletes, inserts to get the data
> back to how I want it but it would be nice if I could just click
> 'backup' and and then later revert. If it was fast it could be a real
> help.

Indeed BACKUP/RESTORE is the easiest way to do this, although it can
take a bit too long time with big databases.

Database snapshots on SQL 2005 overcomes that problem.

For a single test, it often works with putting the entire test in
BEGIN/ROLLBACK TRANSACTION, but this may not always work. As long as
the test succedes there are no problems, but if there is a rollback
in the code being tested, the rest of test may be run without a rollback.
And if the test includes doing things from a GUI, or using multiple
processes, BEGIN/ROLLBACK is not practical at all.


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

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