Undo last statement

[Jack]

Is there anyway in SQL Server to rollback an SQL statement which was
already executed. I know there is a transaction log but what it
contains and how it works is still a mystery to me.
Assuming I delete all records from a table - can I somehow undo this?

[Jens]

Re: Undo last statement

If you don´t have a transaction scope defined around the command:

BEGIN TRANSACTOON
DELETE FROM SomeTable
ROLLBACK --THis does a rollback

...you can´t. You will need to restore your data from a backup.

HTH, jens Suessmeyer.

---

http://www.sqlserver2005.de

---

[Jack]

Re: Undo last statement

So I gathered... So now I ask this question to the empty universe:
Why the heck does SQL Server maintain a transaction log and all the
headaches which go along with it if it can't even be read or used
without 3rd party tools?!?

[Jens]

Re: Undo last statement

The log is the transactional heart of SQL Server. It guarantees
concistency over multiple transactions. If you don´t open a dedicated
one, the transaction you are working in is implicit, so every statement
that you issue will be executed right on hand. If you have a
transactional backup of your database you can do a point-in-time
recovery depending on your recovery model.

HTH, Jens Suessmeyer.

---

http://www.sqlserver2005.de

---

[Hugo Kornelis]

Re: Undo last statement

On 20 Apr 2006 13:11:20 -0700, Jack wrote:
[color=blue]
>So I gathered... So now I ask this question to the empty universe:
>Why the heck does SQL Server maintain a transaction log and all the
>headaches which go along with it if it can't even be read or used
>without 3rd party tools?!?[/color]

Hi Jack,

Of the top of my head:

1. To roll back a change if you DID remember to put a BEGIN TRAN first,
2. To restore a database after an unexpected shutdown,
3. To allow point in time restore,
4. For log shipping.

I've probably forgotten a few as well :-)

--
Hugo Kornelis, SQL Server MVP

[Erland Sommarskog]

Re: Undo last statement

Jack (cawoodm@gmail. com) writes:[color=blue]
> Is there anyway in SQL Server to rollback an SQL statement which was
> already executed. I know there is a transaction log but what it
> contains and how it works is still a mystery to me.
> Assuming I delete all records from a table - can I somehow undo this?[/color]

Two options:

1) Use a third-party tool that is able to read the transaction log
and construct undo batches from it. Two such products are
Lumigent Log Explorer and Log PI.

2) a) Make a note of when the fatal error occurred. b) Backup the
transaction log. c) Restore the last full backup with norecovery.
d) Apply transaction log dumps with a STOPAT just before the
fatal DELETE was done.



--
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