How to retrive truncated records

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to retrive truncated records

Author

Topic

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2006-10-06 : 05:39:34

Hi
I have a table Test a execute this below query

truncate table Test

I want to retrive records of Test table .How can i do.

Ranjeet Kumar Singh

Kristen
Test

22859 Posts

Posted - 2006-10-06 : 05:42:11

You want the records from BEFORE the TRUNCATE ?

You need a restore-from-backup for that.

You can do:

BEGIN TRANSACTION
truncate table Test

then do some stuff - e.g.:

SELECT * FROM TEST

and then either:

COMMIT -- Keep the changes
or
ROLLBACK -- throw away the changes, and revert to the state at BEGIN TRANSACTION

Kristen

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2006-10-06 : 06:45:37

No i want the records After the TRUNCATE ?

Ranjeet Kumar Singh

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-06 : 06:53:25

quote:
Originally posted by ranjeetsingh_6

No i want the records After the TRUNCATE ?

Ranjeet Kumar Singh

You can't !!

The only option you have is to take backup before executing TRUNCATE and restore it later.

Harsh Athalye
India.
"Nothing is Impossible"

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2006-10-06 : 06:55:00

Thanks

Ranjeet Kumar Singh

Kristen
Test

22859 Posts

Posted - 2006-10-06 : 07:09:28

There are no records after a Truncate - that's the whole idea!

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-06 : 09:45:08

quote:
Originally posted by ranjeetsingh_6

Hi
I have a table Test a execute this below query

truncate table Test

I want to retrive records of Test table .How can i do.

Ranjeet Kumar Singh

Herefter before deleting or truncating a table, make sure to take backup then it is easy to get the data back. Also run all your deletes and Truncates in TEST server before applying them in Production server

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-10-07 : 02:18:30

you can also maintain the audit trail on the table.

Have a look at this links
http://www.mindsdoor.net/SQLTriggers/AuditTrailTrigger.html
http://www.nigelrivett.net/SQLTriggers/Triggers_2_Creating_Audit_Trails.html

Chirag

Kristen
Test

22859 Posts

Posted - 2006-10-07 : 03:37:13

... and here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=audit%20changes

Kristen

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2006-10-09 : 02:03:37

back up a table is nothing but export or other?
because i didnt find anywhere backup option on the table when i right click

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-10-09 : 04:56:40

you can not take the backup of the table, you can only take the backup of the whole database.

if you want to take the backup of the table, then create other table with same structure, or by using the Into Query.

Select * Into BackupTable From MainTAble.

or you have to maintain the Audit Trail check out the links which are posted by me and Kristen.

Chirag

http://chirikworld.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources