April 27, 2008 at 11:30 am
Hello all.
I have full set of backups and transaction log backups taken just after midnight for previous day.
I'm trying to restore the database on dummy pc to certain point of time-04-24-2008 17:35 pm.
The problem is that I cannot rollback,because of the order backups were taken first data backup and then trasaction log backup.
Here are the steps I've tried.
1.Restore the data from 04-24-08(backup from previous day) with NoRecovery.
2.Restore log from log backup taken right after 04-24-08 data backup
with NoRecovery
3.Restore log from log backup taken on 04-25-08 to 17:35pm with NoRecovery
(I'm getting error that I have to use previus log file)
Any ideas? And what is the best backup plan for this case scenario
April 27, 2008 at 11:56 am
To restore to a point in time, you have to restore all transaction log backups from the backup you are restoring from to the point in time you are restoring to.
If you have transaction log backups every hour, and your backup was at 1:00am:
- Restore full backup (with norecovery)
- Restore transaction log taken at 2:00am (norecovery)
- Restore transaction log taken at 3:00am (norecovery)
...
- Restore transaction log taken at 5:00pm (norecovery)
- Restore transaction log taken at 6:00pm (recovery, stop at 17:xx)
HTH,
Jeff
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 27, 2008 at 1:39 pm
I just tried one more time and this is the mesage I'm receiving.The log for this backup begins at LSN 97383...33...001,which is too recent to apply to the database.An earlier log backup that includes LSN 97383...44...001 can be restored.I think I now what is the reason for this error.I have another backup that runs after this one and is overwritng itself everynight so I don't have all the trans logs.
Any hope?
April 27, 2008 at 4:51 pm
Lubo Petrov (4/27/2008)
I just tried one more time and this is the mesage I'm receiving.The log for this backup begins at LSN 97383...33...001,which is too recent to apply to the database.An earlier log backup that includes LSN 97383...44...001 can be restored.I think I now what is the reason for this error.I have another backup that runs after this one and is overwritng itself everynight so I don't have all the trans logs.Any hope?
If you are overwriting the transaction log backup every time you run that backup, then the only hope you have is that you are copying the file (or backing that file up to tape) prior to the scheduled transaction log backup.
If not, then you will not be able to restore to a point in time prior to your latest transaction log backup.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 1, 2008 at 9:35 pm
there is two reason why it is not working.
1. you are overwriting the transaction file.... do not add "init" option while taking the trasaction log backup.
2. you have to take the backup of the tail of the log file. Before you try to do the log restoration.
backup log with norecovery.
3 do the restore operation.
if you get problem let me know
thanks
viren chauhan
DBA
July 2, 2008 at 1:53 pm
Thanks for your reply.I posted about this issue while ago.Since then I changed my backup plan so now everything is fine.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply