help me...

Question

Post reply

help me...

dewa_himatif_sf

Grasshopper

Points: 16
More actions
November 13, 2007 at 10:03 pm

#119205

please help me..
i've deleted some record from one table...
how can i undo that process?
help me, that's important data.
thanks

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

Well, that depends on your setup.

1. When was the record you deleted added/inserted into the database?

2. When was your last backup?

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

dewa_himatif_sf Grasshopper Points: 16 More actions · Answer 2

yup, it's out uf my control, i don't want to delete it, but i made a mistake, just wrong in " "

the data from January 2006 till yesterday,

last backup is Nov 9 2007.

i've heard that the data isn't permanent delete, but it's save in .ldf, but idon't know about that

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 3

OK, I'll help as much as possible.

It sounds like you need/want immediate help. If so, do you have MSN Messenger or something of that sort?

If not, you'll need to watch/refresh this post frequently. 🙂 *I've got to hit the sack soon, but I'll do what I can to help out*

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

dewa_himatif_sf Grasshopper Points: 16 More actions · Answer 4

thanks jason...

I'll need to watch/refresh this post frequently

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 5

But you didn't answer my question...

when was the last backup made of the database?

and a few others ...

what's the recovery model of the DB?

is the DB in use 24/7?

First thing, restore that backup to a Development server.

*** note to anyone with experience recovering from tran logs ... jump in please ***

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

dewa_himatif_sf Grasshopper Points: 16 More actions · Answer 6

// when was the last backup made of the database?

November 9 2007

and a few others ...

//what's the recovery model of the DB?

simple

//is the DB in use 24/7?

i don't understand about that

i'm sorry i come from indonesia (bandung)

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 7

dewa_himatif_sf (11/13/2007)
// when was the last backup made of the database?
November 9 2007

You NEED to restore the last backup of the DB to a server. This will at MINIMUM, allow you to restore the data up until Nov. 9

and a few others ...
//what's the recovery model of the DB?
simple

Which means you won't be able to get the data since Nov. 9. IT's GONE, SORRY, BUT IT IS and there is no way around it. **Don't Cry**

//is the DB in use 24/7?
i don't understand about that
i'm sorry i come from indonesia (bandung)

All that I'm asking here is "How often is the database used?" or "Is it critical that it's online all hours of the day and seven days a week?"

But most off all, RESTORE THE LAST BACKUP to another machine, or better yet, the same server that has the table with the records you've deleted.

OK, It's a little late to ask, but are you the DBA? or a developer that just made a mistake and are wanting to fix it before it's discovered? If you're the developer, trust me on this one, you're better off just owning up to the mistake and moving on....

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 8

SORRY dewa_himatif_sf but I've got to sign out for the night. I hope you find the help you need.

I'll check back into this post tomorrow.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

Data might not be gone if the database is in the "FULL RECOVERY" mode... can you check, please? Don't do a recovery, just yet...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

Okidoki... I'm not a "Systems DBA" so anything I'd say would probably be wrong on this... so the following URL's are the step by step instructions on how to do this type of recovery... READ THEM BOTH THOROUGHLY. WHEN YOU THINK YOU UNDERSTAND WHAT NEEDS TO BE DONE, READ THEM AGAIN... THEN MAKE A COPY ON PAPER AS A CHECKLIST AND GIVE IT A TRY.

http://technet.microsoft.com/en-us/library/ms187495.aspx

http://technet.microsoft.com/en-us/library/ms190244.aspx

http://technet.microsoft.com/en-us/library/ms178143.aspx

I suspect the 3rd link above will be what you want, but I got there by going through the first two in order...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

dewa_himatif_sf (11/13/2007)
i've heard that the data isn't permanent delete, but it's save in .ldf, but idon't know about that

All data changes are written to the transaction log. If the DB is in full recovery mode, there are tools available that can recover data from the log.

Since your DB is in simple recovery mode, the log records are marked as inactive and truncated whenever SQL does a checkpoint. Hence, older log records are gone and cannot be recovered.

I would suggest, like Jason said, that you resotore the DB backup that you do have to another server, and see what data you can retrieve.

p.s. If this is an important database, why is it in simple recovery mode? With simple, the best you can do in the case of a disaster is to restore to the last full database backup

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dewa_himatif_sf Grasshopper Points: 16 More actions · Answer 12

IN SIMPLE MODE,

can you help me?

but I've heard that the record from table was delete, isn't not permanent deleted, we can undo "delete proses" if we can read the database log (mydatabse.ldf), but i don't know how make it.

thanks f ur reply 🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

dewa_himatif_sf (11/13/2007)
IN SIMPLE MODE,
can you help me?
but I've heard that the record from table was delete, isn't not permanent deleted, we can undo "delete proses" if we can read the database log (mydatabse.ldf), but i don't know how make it.
thanks f ur reply 🙂

That's only the case in full and bulk-logged recovery modes. In simple the log gets truncated when a checkpoint runs. Depending on the activity of the DB, that'll be anything from once a minute to every 15 or so.

Also, you can't undo from the tran log without 3rd party tools. There's nothing within the SQL Server product that allows you to do that.

If the DB was in full recovery mode, you could restore the DB backup, then restore the log backups stopping just before the delete happened (the links Jeff provided) Since you're in simple and have no tran log backups, you're limited to just restoring the DB backup.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Minaz Amin SSChampion Points: 11052 More actions · Answer 14

I agree with Jeff, If the database is in full recovery model it is possible to restore the deleted data. We do a point in time recovery provided you know what time you have deleted these data.

Please refer the BOL for point in time recovery.

Try this out in the test server first.

Please check these links before proceeding:

msdn2.microsoft.com/en-us/library/ms191455.aspx

msdn2.microsoft.com/en-us/library/ms191468.aspx

Minaz

"More Green More Oxygen !! Plant a tree today"