Solved

Undo a TRUNCATE command

Posted on 2004-09-21
20
2,821 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Solution for warm standby SQL server 20 34
T-SQL: Subtracting Amounts from "Among Rows" 3 41
TSQL previous 5 23
T-SQL: Nested CASE Statements 4 24
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

914 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

22 Experts available now in Live!

Get 1:1 Help Now