Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
srikanth12
Starting Member
22 Posts |
 Posted - 2010-03-08 : 19:43:44
|
| 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
285 Posts |
Posted - 2010-03-08 : 21:46:35
|
| begin trandelete Table1 -- delete Table2 1 - commit tran2 - rollback tranIf 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.ThenIf 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. |
 |
|
|
srikanth12
Starting Member
22 Posts |
Posted - 2010-03-08 : 23:00:02
|
| 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 table1now can we roll back? If YES could you please let me know how we can do that? |
|
|
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2010-03-08 : 23:06:35
|
| 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 |
|
|
|
srikanth12
Starting Member
22 Posts |
Posted - 2010-03-09 : 00:52:07
|
| Perfect!!! That is what i am looking for...........thanks Bohra and NAMMAN. Appreciate your help. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-03-09 : 02:31:16
|
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 ShawSQL Server MVP |
|
|
|
|
|
|
|
|
RollBack after Delete command