Hi Gaurav!
I think you over-estimated Rollback statement. It's not like UNDO or (Ctrl+Z). You can only rollback an activity if it has corresponding BEGIN TRANSACTION sort of thing. If you have executed a statement like DELETE and you want it rolled back, you must start with BEGIN TRANSACTION statement. Something like this -
BEGIN TRAN
DELETE FROM EMPLOYEE WHERE ID=107
...
-- Ist Select statement :
SELECT * FROM EMPLOYEE
ROLLBACK
-- 2nd Select statement :
SELECT * FROM EMPLOYEE
---------------------------------------------------
See, In first select statement, you do not find employee with id=107, After rollback, you find the same row with id=107.
There are 3 modes of transaction in SQL Server -
(1) Auto Commit
(2) Explicit
(3) Implicit
If you are in Implicit Transaction mode, each commit or rollback automatically issues a BEGIN TRANSACTION statement from the next statement.
If you haven't taken care of all these things, issuing a Delete statement can not be rolled back.
( SQL Server's auto commit mode automatically issues begin tran and commit for each statement. If it is not in Implicit and Explicit transaction mode to avoid several anomalies.)
Next hope is Restoring the database. Must be done something like this -
(1) Do not backup your active transactions. If you are in production, you may lose some transactions after last backup (log or datatabase).
(2) Restore the last full backup.
(3) Restore the last differential backup. (if u have)
(4) Restore the tran log backup in sequence.(if u have)
If you are just wondering about such scenario then, it's good for you. So, issue a begin statement before doing anything. But, do not leave it without Commit or Rollback, otherwise, all tranaction would create several locks until you commit or rollback.
- Som Dutt
http://somdutt.blogspot.com
Rollback of Delete
