snapshots and reverts

[codefragment@googlemail.com]

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

[Erland Sommarskog]

Re: snapshots and reverts

(codefragment@g ooglemail.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@sommarsk og.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

[codefragment@googlemail.com]

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.

[Erland Sommarskog]

Re: snapshots and reverts

(codefragment@g ooglemail.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@sommarsk og.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