SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 RollBack after Delete command
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

srikanth12
Starting Member

USA
22 Posts

Posted - 03/08/2010 :  19:43:44  Show Profile  Reply with Quote
Hi All,

I have studied regarding the delete and truncate which confused me regarding the ROLLBACK. What it says is delete can rollback and truncate cannot be rolled back.(I agree with that)... Could anyone of you please let me know how the rollback works with a real time example.
For example:
I have a table with 100 records and unfortunately i deleted them with delete command. Now i want to rollback this. How can we do this? Any responses will be highly appreciated.

namman
Constraint Violating Yak Guru

USA
272 Posts

Posted - 03/08/2010 :  21:46:35  Show Profile  Reply with Quote
begin tran
delete Table1
-- delete Table2
1 - commit tran
2 - rollback tran


If you highlight the first 2 lines and execute them. That starts a transaction, then 100 records of Table1 have been deleted, BUT not completed yet. It means that at that time, for other connections, Table1 still has 100 records, just can not access them because of exclusive lock.

Then
If you highlight "commit tran" and execute it. That ends the transaction, then 100 rerecords really gone for all connections. The only way you can get it back is restoring the database from the last backup.

If you highlight "rollback tran" and execute it. That ends the transaction, then there is like not thing happen to the 100 records. All connection can access them again.

ROLLBACK is mainly used to guarantee the transaction has been performed as a unit. Suppose, the example above have the third line with delete command, ROLLBACK helps to make sure data on both tables have been deleted or there is nothing change in both tables.

ROLLBACK is normally used with try and catch.
Go to Top of Page

srikanth12
Starting Member

USA
22 Posts

Posted - 03/08/2010 :  23:00:02  Show Profile  Reply with Quote
ok...understood with the given example. But what if we do it with out begining the transaction and delete it and then want to roll back?

Example:
delete table1

now can we roll back? If YES could you please let me know how we can do that?
Go to Top of Page

pk_bohra
Flowing Fount of Yak Knowledge

India
1182 Posts

Posted - 03/08/2010 :  23:06:35  Show Profile  Reply with Quote
If the IMPLICIT_TRANSACTIONS mode is On (Value need to be explicitly changed) then you can still issue a rollback statement.

If the IMPLICIT_TRANSACTIONS mode is Off (Default value) then you cannot recover the deleted data.

I guess that in your case the value of IMPLICIT_TRANSACTIONS mode is Off so no command will work directly for getting back the deleted data.

All the deleted transaction are logged in log file. Check out with DBA if they can help you and retrieve back the data from log file.

Regards,
Bohra

Edited by - pk_bohra on 03/08/2010 23:09:17
Go to Top of Page

srikanth12
Starting Member

USA
22 Posts

Posted - 03/09/2010 :  00:52:07  Show Profile  Reply with Quote
Perfect!!! That is what i am looking for...........thanks Bohra and NAMMAN. Appreciate your help.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37119 Posts

Posted - 03/09/2010 :  00:54:56  Show Profile  Visit tkizer's Homepage  Reply with Quote
The only way to rollback in your example is to restore the database. Hopefull you aren't using SIMPLE recovery model and have tlog backups that cover your timespan.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 03/09/2010 :  02:31:16  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
quote:
Originally posted by srikanth12

What it says is delete can rollback and truncate cannot be rolled back.(I agree with that)


TRUNCATE can be rolled back. It's a logged operation (page deallocations logged) and a rollback will affect it just like any other operation.

--
Gail Shaw
SQL Server MVP
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000