November 13, 2003 at 2:19 am
Is there a way to recover the backup history for a database and transaction log? I inadvertently deleted the backup history while trying to restore and recover in a Full Recovery model. I need to do a "point-in-time" restore of the log.
I still have copies of the mdf and ldf as they were before the process began. I also have nightly backup and transaction log backup.
I have msdb and master backups and their log backups from nightly backup.
When I deleted the history, the date modified on the msdb.mdf did not change for some reason. Neither did the date modified on the msdb.ldf.
Could I possibly return the backup history by restoring msdb and/or master?
Please keep in mind that I need to do a "point-in-time" or STOPAT restore when I get to the transaction log. I only have one transaction log backup since the last database backup. It was done AFTER the data mistake. I am not doing differential backups.
Please help!!!!
Gerald
November 13, 2003 at 7:50 am
AS far as I know the restore history is in msdb and you can restore this from backup to a different database name - just so you can get at the data as I have done to recover data in the past. This way you preserve your 'pucker' msdb for that instance.
Hope this helps
November 13, 2003 at 8:12 am
GeraldH,
before you start to do any recovery, i suggest that we (everyone who is on-line and readind this post) together make a plan first, a clean steps, test on other server(s).
xyz.
November 13, 2003 at 8:14 am
Thanks. It appears that there is another trans log backup happening via Veritas to a tape that I didn't know about until now. That's why I couldn't get the restore/recovery process to work. I have to wait a couple hours to speak to the person who set up that backup. My suspicion is that the Veritas backup is truncating the log. If so, I guess there's no way that my recovery will work, right?
Gerald
November 13, 2003 at 8:18 am
While you are waiting, could you list your database backup time list?
November 13, 2003 at 8:28 am
Thanks, xyz, I really appreciate it.
I assume you mean the following:
**Full Recovery Model
1) 2:00AM - full db backup to local disk
2) 9:45AM - full db backup via Veritas to tape
3) 9:46 AM - log backup via Veritas to tape
4) 4:30PM - unwanted data update
5) 4:40PM - log backup to local disk
Gerald
November 13, 2003 at 8:36 am
what time you had cope of .mdf and .ldf
November 13, 2003 at 8:55 am
Here is a revised timeline:
**Full Recovery Model
1) 2:00AM - full db backup to local disk
2) 9:45AM - full db backup via Veritas to tape
3) 9:46 AM - log backup via Veritas to tape
4) 4:30PM - unwanted data update
5) 6:00PM - detach db
6) 6:02PM - make copy of detached mdf and ldf
7) 6:02PM - re-attach db
5) 6:15PM - log backup to local disk
I didn't realize the detaching and reattaching could have affected my restore/recovery process.
Gerald
November 13, 2003 at 9:07 am
i guess you want go back before 4:30PM the update.
if so, here is the plan A:
restore your db
from [9:45AM - full db backup via Veritas to tape]
with recovery,
file = ?
or plan B:
restore your db
from [2:00AM - full db backup to local disk
]
with recovery,
file = ?
if you want go back after 4:30PM, the plan C:
1.stop server;
2.rename current .mdf and .ldf
3.move the copies of .mdf and ldf
4.start server
** i did this way 2 years ago, i don't know it will work today.
please let me know if i'm wrong.
Edited by - xyz on 11/13/2003 09:08:49 AM
November 13, 2003 at 9:16 am
Actually, what I want is a point-in-time recovery to right before the 4:30PM incident. Do you think this is still possible?
Gerald
November 13, 2003 at 9:32 am
how about this way:
restore your db
from [9:45AM - full db backup via Veritas to tape]
with NOrecovery,
file = ?
restore your log
from [9:46 AM - log backup via Veritas to tape]
with NOrecovery,
file = ?
restore your log
from [6:15PM - log backup to local disk
]
with Recovery,
STOPAT = '2003-mm-dd 16:29:00',
file = ?
i guess you did this way already without luck.
Edited by - xyz on 11/13/2003 09:36:51 AM
November 13, 2003 at 11:12 am
FYI.
PRB: Filegroup Log Restore May Fail with Error Message 4305
http://support.microsoft.com/default.aspx?scid=kb;en-us;295371
November 13, 2003 at 2:50 pm
Thanks, xyz, the backup tape did not have the log I needed. I am waiting for the previous backup tape to be retrieved. It was probably switched right after the log backup.
Gerald
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply