stuck with restore

Question

stuck with restore

MW-309385

Old Hand

Points: 341
More actions
June 23, 2008 at 8:51 am

#381295

Folks, here is the issue
1. The database does not have any full backup so far
2. User deleted (dropped) a table.
3. I need to recover the accidently deleted data.
Here is what I did
1. Created a full backup ( did this way after the deletion took place )
2. Created a transaction log backup ( too late?).
3. Now what ?
Why the heck can't we just read the active transaction log and rollback certain transactions?

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

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

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 1

Well, it really depends on the mode that the DB was in when you ran the delete.

You may have screwed yourself with the log backup though. If you were in full recovery mode, you may still be able to pull it out.

Restore from the full backup, maybe that portion of the log is still there. If it is, then go download yourself one of those log parser tools. Lots of people make them. ApexSQL has one, LiteSpeed has one, Lumigent, etc... Just search for sql server log parser online and you should find yourself with plenty of choices.

At this point that's pretty much your only option that I know of. And had you not taken that log backup you'd be better off than you are now.

Good luck with this one.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

MW-309385 Old Hand Points: 341 More actions · Answer 2

Thanks

1. I do have the original mdf file

2. I have the original ldf file.

Regerdless of the backup status, I can recreate the databse ( I mean by using the for attach option. )

Let me put ot this way. If we have a

1. DB with no backup done so far.

2. User deleted some data accidently.

Question? Can we not recover the deleted data ?

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 3

The short answer to that specific scenario is I don't know. I could honestly see it going either way. However, you're talking about recovering a specific transaction, so the thing you'll have to do if you want to use SQL native tools is restore the full backup using the STOPAT command and pick a time to before the data was deleted. Then the data will be there because the transaction wouldn't have happened yet. However, if you need to retain other data from that backup, then you'll need to pull the data out of the log with a log parser.

So try this... if you've got the space, find out when the delete was done and then restore the DB under another name with the STOPAT flag and pass it a time before that delete. Then just see if it's there.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

MW-309385 Old Hand Points: 341 More actions · Answer 4

Sorry folks:

I guess I have not explained this properly. Lets put it this way.

1. DB created on June 1 2008.

2. Users add data, modify do what ever....

3. User accidently drops a table on June 19, 2008 8 PM

4. DBA realizes that there is no backup whatsoever.

What can the DBA do from this point onwards.

1. He can not backup the TR LOG because a full backup was not done so far.

What would you advice him ?

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 5

OK, now maybe I'm not explaining myself clearly either. Try what I told you to and get back to us.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

MW-309385 Old Hand Points: 341 More actions · Answer 6

So basically, here is what I got.

1. Look for a SQl server log parser.

2. Download, install and run against the live database.

3. Use the tool to extract data from the tr log.

I guess doing backups at this moment ( after the deletion was done )

will not be of any use.

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 7

Take a look at what I said above. Here, I'll paste it in here so you know what I'm talking about.

The short answer to that specific scenario is I don't know. I could honestly see it going either way. However, you're talking about recovering a specific transaction, so the thing you'll have to do if you want to use SQL native tools is restore the full backup using the STOPAT command and pick a time to before the data was deleted. Then the data will be there because the transaction wouldn't have happened yet. However, if you need to retain other data from that backup, then you'll need to pull the data out of the log with a log parser.

So try this... if you've got the space, find out when the delete was done and then restore the DB under another name with the STOPAT flag and pass it a time before that delete. Then just see if it's there.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

MW-309385 Old Hand Points: 341 More actions · Answer 8

Thanks

See whether you would agree with the following ?

RESTORE DATABASE Northwind

FROM DISK = 'C:\Backups\Northwind_Full_20080623.BAK'

WITH NORECOVERY

RESTORE LOG Northwind

FROM DISK = 'C:\Backups\Northwind_Log_20080623.BAK'

WITH STOPAT = N'06/18/2008 6:23:36 PM'

Note: I did both backups today (for the very first time ). The data was deleted on 6/19/2008

You think it will still work ?

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 9

In your case you'll want to use the STOPAT flag with the full restore, not the log restore.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

MannySingh SSChampion Points: 12465 More actions · Answer 10

Check out PAUL RANDAL's BLOG at sqlskills.com, Lots of info about Disaster Recovery and internals

Maninder
www.dbanation.com

MW-309385 Old Hand Points: 341 More actions · Answer 11

Sorry it did not work. Throws an error. See the command I use and the msg below.

RESTORE DATABASE Reporting

FROM DISK = 'C:\Reporting\Reporting.BK'

WITH STOPAT = N'06/18/2008 5:23:36 PM'

I get the message.. below...

Msg 4338, Level 16, State 1, Line 2

The STOPAT clause specifies a point too early to allow this backup set to be restored. Choose a different stop point or use RESTORE DATABASE WITH RECOVERY to recover at the current point.

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 12

Yeah, I was afraid of that. Unfortunately I think you're screwed. You may be able to get a log parser and find it, but I really wouldn't hold my breath.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

David Webb-CDS SSCoach Points: 17398 More actions · Answer 13

What recovery model was the database in before you backed it up. I'm guessing it was in 'simple' mode, which means the logs were truncating every few minutes (clearing out committed transactions). If that's the case, the log information you need is long gone and there is no way to recover it that I know of.

And then again, I might be wrong ...
David Webb

MW-309385 Old Hand Points: 341 More actions · Answer 14

Hello

Getting a copy of the original transaction log is no problem.

It is not trunacted. I can always get a copy from the prod server.

The size of my transaction log file is much larger that the mdf file.

So this means the data is there. It's a matter of getting it out.

Thank GOD I have the original LDF and MDF file to work with.

Now my luck is on the log parser tools..

Any comments.... appreciated.