Solved

Undo a TRUNCATE command

Posted on 2004-09-21
20
2,840 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: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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
kill process lock Sql server 9 45
SqlServer no dupes 25 34
Help creating a spatial object in SQL Server 4 20
SSRS Enable Remote Errors 4 23
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

813 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