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
Go to Top of Page

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
Go to Top of Page

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"
Go to Top of Page

ranjeetsingh_6
Posting Yak Master

125 Posts

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

Ranjeet Kumar Singh
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-10-06 : 07:09:28
There are no records after a Truncate - that's the whole idea!

Kristen
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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/
Go to Top of Page
   

- Advertisement -