Rollback a delete or truncate statements. 1

[jsql12]

Hi there,

I'd like to know how to rollback:
1- Delete from table
2- Truncate table

Your help is greatly appreciated

 

[SQLDenis]

easy, you have to start a transaction then do a rollback


begin tran a
delete tableb
truncate table tableb
rollback tran a

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[ptheriault]

I didn't think you could rollback a truncate statement? I was under the impression that it is not logged the same way as a delete would be logged in the TLOg.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLDenis]

sure you can

create table tableb (id int)
insert tableb values(1)

select *,'before tran' from tableb
begin tran a

truncate table tableb
select *,'after truncate' from tableb
rollback tran a 

select *,'after tran' from tableb

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[SQLDenis]

a truncate is still mimimally logged what that means is that the pointers to the pages are dealocated

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[ptheriault]

Thanks for that Denis. I always thought you couldn't do that.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

a truncate is still mimimally logged what that means is that the pointers to the pages are dealocated

IF you have a lool like lumigent can you re-create or undo the truncate?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLDenis]

don't know

yes I do

Recover data for a table that was accidentally dropped or truncated. Whoa, this is powerful stuff. Log Explore can recover tables that you have accidentally or purposely obliterated. Not only can tables be recovered from the log, but from SQL Server backups. This is a feature that even SQL Server does not provide. As you can see, Log Explorer is a powerful data recovery tool that can save your a lot of time.

read more here:

http://www.sql-server-performance.com/log_explorer_spotlight.asp

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[jsql12]

Thanks for your quick responses.
The situation is when the table was deleted/truncated, the statements were not inside a transaction x. The person that deleted or truncated the table did so by issuing in the query analyzer the following statements:
Delete from Table a
Truncate Table a
(or worse) Drop Table a
I don't know how to rollback these statements. Is there any way other than restoring from a backup.
Thanks

 

[ptheriault]

yes,
1.Save the transaction log now. Backup it up!
2. get lumigent log explorer.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLDenis]

>>1.Save the transaction log now. Backup it up!

This is where most people say "we don't have a backup"

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[nice95gle]

Denis,

Wait one min...Next you'll tell me there's no Santa Clause. Since when can you rollback a truncate? How does this work? The reason I'm asking is because our so called IS team is have some issues with the trans logs and requesting all deletes be change to truncate where acceptable to lessen the load on the logs.

Well Done is better than well said
- Ben Franklin

 

[jsql12]

I guess I'll just backup the log and do a restore from the previous backup I have.

Thanks for he help.

 

[SQLDenis]

>>Since when can you rollback a truncate?

always within a tran, run this

create table tableb (id int)
insert tableb values(1)

select *,'before tran' from tableb
begin tran a

truncate table tableb
select *,'after truncate' from tableb
rollback tran a 

select *,'after tran' from tableb

it is true that a truncate will be much faster because the actual delete statement is not logged just where the row is located. That is the reson the log doesn't grow as much

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[nice95gle]

Thanks

I should of known that, because if I put a trunc in a nested trans and rollback the outer most trans I can undo the change. I wasn't thinking.

Well Done is better than well said
- Ben Franklin