Solved

Undo a TRUNCATE command

Posted on 2004-09-21
20
2,800 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
  • 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:ScottPletcher
ID: 12113890
Do you have db and log backups?
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher 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
 
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:ScottPletcher
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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:ScottPletcher
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:ScottPletcher
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now