By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,575 Members | 1,968 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,575 IT Pros & Developers. It's quick & easy.

Undo last statement

P: n/a
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?

Apr 20 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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
---

Apr 20 '06 #2

P: n/a
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?!?

Apr 20 '06 #3

P: n/a
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
---

Apr 20 '06 #4

P: n/a
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
Apr 20 '06 #5

P: n/a
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
Apr 20 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.