truncated table accidently - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

truncated table accidently

Author

Topic

shekhar_dba
Starting Member

39 Posts

Posted - 2006-04-24 : 16:20:36

hi gurus

i have truncated table accidently and i dnt have backup for the same.
i wanna know Is there any way from where i can get back that table and data?

please treat it to b most urgent

regards
shekhar

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-24 : 16:30:50

Since you don't have a backup, the only way to get your data back is to use a third party tool such as Lumigent's Log Explorer which can read your transaction log. This assumes you have the transactions in the log still which assume you aren't using SIMPLE recovery model.

Tara Kizer
aka tduggan

shekhar_dba
Starting Member

39 Posts

Posted - 2006-04-24 : 16:33:53

hi tara
Is there any other way to come out with this problem ? actually i dnt have this third party tool.

regards
shekhar

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-24 : 16:35:59

You'll need to at least download the trial version, if they have one available; otherwise you'll need to purchase it. Red Gate has a similar too. It might be cheaper.

From now on, make sure you have backups!

Tara Kizer
aka tduggan

shekhar_dba
Starting Member

39 Posts

Posted - 2006-04-24 : 16:39:12

ok thanks alot tara for ur valuable tip ..... i will try to download trail version of this utility. but Is that true that we dont have any option available in SQL SERVER for the problem ?

regards
shekhar

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-24 : 16:42:14

Your option was to backup your database and then restore it like in this situation. You have limited your options due to not backing up your database.

Tara Kizer
aka tduggan

shekhar_dba
Starting Member

39 Posts

Posted - 2006-04-24 : 16:43:38

ok thanks once again ... i will remeber these things in future :(

regards
shekhar

Kristen
Test

22859 Posts

Posted - 2006-04-24 : 17:12:51

"This assumes you have the transactions in the log still which assume you aren't using SIMPLE recovery model"

Assuming FULL Recovery Model Tara couldn't shekhar_dba take a Transaction Log backup at this point, and then restore the last full backups plus all Transaction Log backups (or just the one you just made, if that's the ONLY one) up to the point just before the accidental truncation?

Or am I being thick and missing something?

If that is a runner shekhar_dba could do this restore to a temporary database, and then "re-instate" the rows in the truncated table by copying them from the temporary recovered database.

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-24 : 17:14:31

shekhar_dba doesn't have a full backup. You can't restore a transaction log without a starting point of a full backup. So no.

Tara Kizer
aka tduggan

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-04-24 : 17:21:01

From Books Online:


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.

So, what guarantee/probability is there that these pages can even be "re-allocated", and how would you go about doing it? And what happens with any Identity values on the table? TRUNCATE resets the Identity counter to 1 again.
I'd hold out very slim hopes of this guy getting his data back.

Kristen
Test

22859 Posts

Posted - 2006-04-24 : 17:22:34

"shekhar_dba doesn't have a full backup"

Darn it ... I re-read the thread several times knowing you would be right, and couldn't see the crucial reason why my plan wouldn't work. Of course now I re-read it once more I can see it plain as day:

"i dnt have backup for the same"

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-24 : 21:22:16

there is still a chance if he downloads the new version of lumigent from apexsql, though it'll be a lot of hard work
i worked on the previous versions and had to pick up rows and rows of data

which actually is the purpose of this entire exercise

--------------------
keeping it simple...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-04-25 : 19:29:24

quote:
So, what guarantee/probability is there that these pages can even be "re-allocated", and how would you go about doing it?

Ummmm....

quote:
take a Transaction Log backup at this point, and then restore the last full backups plus all Transaction Log backups (or just the one you just made, if that's the ONLY one) up to the point just before the accidental truncation?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-04-25 : 23:26:58

So, when a table is truncate and the page deallocation is records in the log, is the actual DATA stored, or just the fact that (and perhaps the locations of) pages deallocated?
And if only the event is recorded, and not the data, then how volatile are the pages and what is the likelyhood that they still exist and hold the required data?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-04-26 : 01:10:24

very and zero

"up to" become very important words.

I would like to run a test of this someday though and see if MS will come up with some nifty way to recover it anyway. The only real issue is that it would probably have to be on production data, so I'm not feeling that skippy today.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-04-26 : 09:00:18

I was hoping careful reading would make this obvious...

You restore a full backup, the data is there. You restore the log up to the moment before the truncation, so the pages haven't been deallocated yet. This stops any sequence of events that would deallocate and reuse those pages.

It's worth experimenting with the process, so that you're comfortable with it if you ever do an emergency recovery. That way you don't make a mistake out of pressure, fear, lack of knowledge/experience, and so on.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-26 : 10:57:39

quote:
Originally posted by robvolk

I was hoping careful reading would make this obvious...

You restore a full backup, the data is there. You restore the log up to the moment before the truncation, so the pages haven't been deallocated yet. This stops any sequence of events that would deallocate and reuse those pages.

It's worth experimenting with the process, so that you're comfortable with it if you ever do an emergency recovery. That way you don't make a mistake out of pressure, fear, lack of knowledge/experience, and so on.

Yes, but there is nothing like an emergency to create clarity and focus.

I rememember someone doing this at 5:00 pm on a Friday. My concern was not that we would not be able to recover the data; it was that the recovery would delay me getting to the bar to meet friends for happy hour.

We recovered in record time with no loss of beer. The data was OK too.

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-04-26 : 16:02:49

"You restore a full backup, the data is there. You restore the log up to the moment before the truncation, so the pages haven't been deallocated yet"

Slight flaw with this is that even if you can make, and have made, a now¤t TLog backup this user has said that they have no Full Backup!

Kristen

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-04-26 : 18:22:49

True. I was addressing blindman's question about deallocation rather than the submitter's actual problem.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-26 : 18:29:07

I think everyone has just been reluctant to come out and tell shekhar that his data is gone and it's not coming back.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources