stuck with restore

Question

stuck with restore

Viewing 10 posts - 16 through 24 (of 24 total)

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

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

Now I'm a little confused. The 4338 message seems to indicate that the stop point you want to get to isn't in the log anymore. Does the table exist on a production server somewhere? If so, why not just copy it from there? You can't use a log from one database to recover against another database. If you have a log with the data contained in it, you might be able to use one of the third party log parser tools to reclaim the data.

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

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

Again let me explain :

1. A dtabase sists in a prod environment

2. This db does not have a master backup

3. On 6/18 data was deleted accidently.

4. I copied the mdf and ldf files to a separet box.

5. Used the "for attach" clause in restore to restore the database.

6. So I have a copy of the database as it is in the prod env

7. Created a full backup

8. Created a tr log backup using the "NO_TRUNCATE" option

9. Tried to restore the db again using the master backup using the

STOPAT command. - it failed!

You said I should copy the table. Hmm the table is there but the

records are gone. I need the records.

Does this help to understand what is going on ?

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

If you copied the ldf after the damage was done and the production database was in 'simple' recovery mode, the data is gone and can't be recovered. The backup you took would also be useless in that case. From the message you got when using the STOPAT syntax, that's what appears to have happened. The log doesn't have the information from a time before the data was deleted.

I'm not really an expert in this area, so I'll defer to wiser folks, but I don't think you can get the data back with what you have available.

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

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 4

Answer me this, what is the current recovery model of the Production Database? If it is full recovery, you will need some kind of log reading software to read the transaction log to find the lost data.

😎

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

APEXSQL log seems like a promising software. Lets see how it goes.

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

DONE !!!!!

😛

APEXSQL tool did the job. Great! It finds all the droped tables, then finds all the inserts that went to the table to a selected time period.

The tool comes with a 30 day trial period! A must! and easy to use!!!

Thank GOD I didn't give up.

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 7

Excellent dude!

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

Minion Maintenance is FREE:

GRE (Gethyn Ellis) SSCrazy Eights Points: 9602 More actions · Answer 8

Congrats on getting the data back, now is proably a good time for you to implement a proper backup strategy.

Gethyn Elliswww.gethynellis.com

2 Tim 3:16 SSCarpal Tunnel Points: 4481 More actions · Answer 9

Some thoughts on this; A full backup after the fact will not include the deleted data. A tran backup cannot be taken until a full backup is taken. The tran backup would be no good because the Log Sequence Number will be after the full backup.

The STOPAT parm can only be used on a log backup (not a full or differnetial).

I believe the only option here is to use a log parser product like Apex, Lumigent, etc... If this is a SQL 2000 system, Red Gate has a freeware.

Tim White

2 Tim 3:16 SSCarpal Tunnel Points: 4481 More actions · Answer 10

I can't see my previous post, so I'll post again.

Some thought on this issue:

A full backup after the fact will not contain the deleted data. You cannot take a tran log backup without first having a full backup. A tran log backup (after a full) would have a log sequence number beginning after the full backup, so again, no data.

You cannot use the STOPAT parm for full or differential restores. The TOPAT is only for a tran log backup/restore.

I believe the only solution here is to use one of the log reader products mentioned; Apex, Lumigent or if this is a SQL 2000, Red gate has a freeware log reader.

Tim White