Undo a TRUNCATE command

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
LVL 1
benfinkelAsked:
Who is Participating?
 
Jay ToopsConnect With a Mentor Commented:
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
 
benfinkelAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
Do you have db and log backups?
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
benfinkelAuthor Commented:
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
 
Jay ToopsCommented:
restore it from backup
Jay
0
 
Jay ToopsCommented:
That is the ONLY way ..
truncate by DEFINITION does not log
even if you get a log manager your table's toast.

JAY
0
 
Scott PletcherSenior DBACommented:
Truncation *DOES* log, but only pages, not rows.
0
 
jdlambert1Connect With a Mentor Commented:
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
 
benfinkelAuthor Commented:
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
 
jdlambert1Commented:
>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
 
Jay ToopsCommented:
LOL.. for a price ...
0
 
benfinkelAuthor Commented:
Well,

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

Appreciate the information!

--Ben
0
 
jdlambert1Commented:
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
 
Jay ToopsCommented:
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
 
jdlambert1Commented:
Do you have a pointy-hair boss, Jay?  :)
0
 
Jay ToopsCommented:
no we have dogbert IT and Catbert management.

Jay
0
 
Scott PletcherSenior DBACommented:
>> 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
 
Scott PletcherSenior DBACommented:
CORRECTION:

which I made +clear+ it did *not* do.
0
 
Jay ToopsCommented:
Cool... have a good one !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.