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
 Old Forums
 CLOSED - General SQL Server
 Trunate Table

Author  Topic 

danasegarane76
Posting Yak Master

242 Posts

Posted - 2006-09-19 : 01:42:07
Dear Team ,
I have used the command
Truncate Table trn_update , and removed the datas from the table.Is there any way to retrive the deleted data?

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-09-19 : 01:47:25
From a backup

Go to Top of Page

danasegarane76
Posting Yak Master

242 Posts

Posted - 2006-09-19 : 01:50:35
I don't have any back up.What is about Rollback Command
Go to Top of Page

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-09-19 : 02:14:15
That's only applicable if you're in the middle of a transaction.

Which I suspect you're not.

Sorry - you've probably lost your data. Hope it wasn't critical/production data
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-09-19 : 03:05:45
ummm, update your resume my friend. Then do some reading in BOL prior to running commands like truncate table.


-ec
Go to Top of Page

danasegarane76
Posting Yak Master

242 Posts

Posted - 2006-09-19 : 03:29:57
Will The command, "delete from " ,can be used with the Rollback
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 03:36:14
Yes.
But only if you do a BEGIN TRAN before the deletion.

Taken from BOOKS ONLINE
quote:
TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table. But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE.

The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.

TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.

You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

TRUNCATE TABLE may not be used on tables participating in an indexed view.

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

mr_mist
Grunnio

1870 Posts

Posted - 2006-09-19 : 05:29:48
quote:
Originally posted by danasegarane76

Will The command, "delete from " ,can be used with the Rollback



Personally I suggest that you don't run any commands at all until you start taking regular back-ups of your databases. I'm presuming that this isn't a business-critical server being as you seem to be experimenting with it. If it is then god help you.

-------
Moo. :)
Go to Top of Page
   

- Advertisement -