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
 General SQL Server Forums
 New to SQL Server Programming
 How to retrive truncated records		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ranjeetsingh_6
Posting Yak Master

India
125 Posts
Posted - 10/06/2006 :  05:39:34  Show Profile  Reply with Quote
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

United Kingdom
22191 Posts
Posted - 10/06/2006 :  05:42:11  Show Profile  Reply with Quote
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
Edited by - Kristen on 10/07/2006 03:35:43
Go to Top of Page

ranjeetsingh_6
Posting Yak Master

India
125 Posts
Posted - 10/06/2006 :  06:45:37  Show Profile  Reply with Quote
No i want the records After the TRUNCATE ?



Ranjeet Kumar Singh
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts
Posted - 10/06/2006 :  06:53:25  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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

India
125 Posts
Posted - 10/06/2006 :  06:55:00  Show Profile  Reply with Quote
Thanks

Ranjeet Kumar Singh
Go to Top of Page

Kristen
Test

United Kingdom
22191 Posts
Posted - 10/06/2006 :  07:09:28  Show Profile  Reply with Quote
There are no records after a Truncate - that's the whole idea!

Kristen
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22460 Posts
Posted - 10/06/2006 :  09:45:08  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1907 Posts
Posted - 10/07/2006 :  02:18:30  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
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

United Kingdom
22191 Posts
Posted - 10/07/2006 :  03:37:13  Show Profile  Reply with Quote
... and here:

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

Kristen
Go to Top of Page

sunsanvin
Flowing Fount of Yak Knowledge

India
1256 Posts
Posted - 10/09/2006 :  02:03:37  Show Profile  Send sunsanvin a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1907 Posts
Posted - 10/09/2006 :  04:56:40  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
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
  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.08 seconds. Powered By: Snitz Forums 2000