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

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

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

On 20 Apr 2006 13:11:20 -0700, Jack wrote:

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

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

Jack (ca*****@gmail.com) writes:

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?

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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx