Solved

Undo a TRUNCATE command

Posted on 2004-09-21
20
2,958 Views
Last Modified: 2008-01-16
I *just* five minutes ago ran a TRUNCATE statement on my table, followed immediatley by an INSERT statement.
I need to get back to the table prior to it being TRUCNATED.  Any way I can do this?

Thanks,

Ben
0
Comment
Question by:benfinkel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 4
  • +1
20 Comments
 
LVL 1

Author Comment

by:benfinkel
ID: 12113848
P.S. - I would even be willing to delete the LDF file and have the server rebuild it if that would save my data.

Thanks,

Ben
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 12113890
Do you have db and log backups?
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 12113899
If not you're pretty much out of luck except for third-party software, such as from Lumigent or Red-Gate (? not sure if they have a product for this specific problem) and probably others.
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 1

Author Comment

by:benfinkel
ID: 12113929
I was afraid of that.

I'm downloading Log PI right now, hoping that the eval version will do what I need to do.

That aside, any recommendations from the experts?

Thanks,
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12113935
restore it from backup
Jay
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12113945
That is the ONLY way ..
truncate by DEFINITION does not log
even if you get a log manager your table's toast.

JAY
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 12114001
Truncation *DOES* log, but only pages, not rows.
0
 
LVL 15

Assisted Solution

by:jdlambert1
jdlambert1 earned 200 total points
ID: 12114012
The main difference between TRUNCATE and DELETE is that TRUNCATE doesn't log all the disappearing data.

Lumigent's Log Explorer can help, but time is of the essence. From their FAQ:

"I do not have a backup of my database and have inadvertently dropped (truncated) a table?  What can Log Explorer do for me?
Log Explorer’s "Salvage Dropped/Truncated" function will also work even if you do not have a backup of your database. The number of rows recovered will depend on how much database activity has occurred since the table was dropped (truncated).  The sooner you act, the more data you will recover.  In short, Log Explorer searches the "free page" list maintained by SQL Server to determine what data has been marked for reclamation but not yet reused.  At the end of the process, Log Explorer generates a recovery script file and provides a report of the total number of rows that were deleted and the number that are actually recoverable."
0
 
LVL 1

Author Comment

by:benfinkel
ID: 12114072
That sounds great.  I've cut everyone off of the database to reduce the possibility of anything else happening.  I'd have to purchase Lumigent's software though, and I'm in a little bigger hurry than that.

I've got an MDF/LDF backfrom from 9/8/04, but that loses about 1 and 1/2 weeks of data.  Anyway I can combine an old log with a new MDF or vice versa?

Thanks more,
Ben
0
 
LVL 10

Accepted Solution

by:
Jay Toops earned 200 total points
ID: 12114156
Scott --

TRUNCATE

"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."

ONLY LOGS PAGE DEALLOCATIONS..
NOT THE PAGES THEMSELVES..

the restore function actually re-attaches the dropped pages.
but it can only restore those pages that were not re-used.

even if he gets the log restore piece he could have data losses in a checkerboard pattern
in his db..

OWCH...

JAY
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12114239
>Anyway I can combine an old log with a new MDF or vice versa?

Not with any Microsoft products. Log Explorer can let you mix and match, because it lets you create scripts for everything. You can use it to script whatever's intact in the db files, and do a restore of the table to a temporary db, then script that table, and combine the scripts.

Lumigent is used to working with companies with a database crisis, so they may be able to help you expidite a purchase.
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12114266
LOL.. for a price ...
0
 
LVL 1

Author Comment

by:benfinkel
ID: 12114274
Well,

Thanks guys, I'll divee up the pts appropraitely.

Appreciate the information!

--Ben
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12114332
I've tried getting companies to buy Log Explorer as a great tools every DBA should have. No one's ever agreed. But when it was the only choice to avoid losing data, or limiting the data lost, they whine, but they pay.
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12114368
LOL........ I hear that one... the company i work for just told me I can't install the developer options
on access (that come with access) because im not in the IT group..

even though im a better developer than anyone they've got..

Jay
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12114396
Do you have a pointy-hair boss, Jay?  :)
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12114424
no we have dogbert IT and Catbert management.

Jay
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 12115184
>> ONLY LOGS PAGE DEALLOCATIONS..
NOT THE PAGES THEMSELVES..  <<

D'uh, that's exactly what I meant, and what I essentially said.  If it recorded the whole page, it would have recorded the rows, right, which I made it did *not* do.

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 12115219
CORRECTION:

which I made +clear+ it did *not* do.
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12115787
Cool... have a good one !
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

634 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question