Replaying the transaction logs after recovery
-
Hi all;
I am doing a snapshot backup and it works fine.
It is actually a full backup (mean BACKUP DATABASE statement).
When doing restore from snapshot I am taking the database offline then doing rollback on the snapshot and taking the database online again.
Everything works perfect.
However, I can only return to the last Full backup point in time, my question is:
What should I do in order to be able to return an old FULL bakup version and then replay the transaction logs until the current time (Under the assumption that I am not clearing any logs and that I have all the logs since that Full backup).
Note:
Before doing restore I am copying the database and the transaction logs files to a temporary folder.
-
I assume by 'rolling back' you mean restoring.
But anyways...to do a 'point-in-time' restore, you need a FULL Backup and at least one transaction log backup (if you have more, they must be restored in order).
RESTORE DATABASE dbname
WITH NORECOVERY
RESTORE LOG dbname
WITH STOPAT date and time
WITH RECOVERY
Any restores done before the LAST log file MUST have WITH NORECOVERY.
Refer to the Books OnLine for information on the options WITH RECOVERY and WITH STOPAT.
-SQLBill
-
Is there any way to find the point in the backup logs that the restore log with recovery statement will work?
quote:
Any restores done before the LAST log file MUST have WITH NORECOVERY -
You can recover at any point. Typically you apply logs up until a 'stopat' time, generally a minute before whatever bad happened. At that point you complete the recovery.
Andy
-
Hi again;
First of all, thanks for the replies, however,
As far as I understand there is no way to play the logs forward unless you have at least one transaction log backup.
Mean:
With only Full backup, all you can do is to get back to the point of the backup.
Am I correct?
-
I am not using the snapshot of the sql, I am using the VDI in order to do a Disk\partition snapshot (The disk where the MDF and LDF files located)
By saying rollback I meant: bringing the disk back to the way it was at the snapshot point in time.
-
quote:
As far as I understand there is no way to play the logs forward unless you have at least one transaction log backup.Mean:
With only Full backup, all you can do is to get back to the point of the backup.
Am I correct?
I think you are right. No way to forward.
I have been palying for weeks with restore database file group from file group backup.
It is very trick and sensitive on the log's LSN. I feel so blind to restore the log back.
I missed the sql6.5. you can restore the table easily back.
Edited by - xyz on 11/19/2003 10:43:50 AM
-
quote:
Hi again;First of all, thanks for the replies, however,
As far as I understand there is no way to play the logs forward unless you have at least one transaction log backup.
Mean:
With only Full backup, all you can do is to get back to the point of the backup.
Am I correct?
You are correct. Without a tran log backup, there is nothing to "play back" to a point in time. There's obviously no way to "play forward" without a subsequent log backup; where would the transactions possibly come from?
--Jonathan
--Jonathan -
quote:
I am not using the snapshot of the sql, I am using the VDI in order to do a Disk\partition snapshot (The disk where the MDF and LDF files located)By saying rollback I meant: bringing the disk back to the way it was at the snapshot point in time.
The vendor of your storage system should have utilities for this.
--Jonathan
--Jonathan -
quote:
There's obviously no way to "play forward" without a subsequent log backup; where would the transactions possibly come from?--Jonathan
The transactions logs are exist cause I copy them to a temporary place before doing the Full Restore.
-
quote:
The transactions logs are exist cause I copy them to a temporary place before doing the Full Restore.Is your database mode in full or simple?
Confession:
I tested yesterday, if the database turned to simple after the backup, the previous backup log would be off set. You will see the error 4305.
One more thing, the database restore has to use the backup file that was created after a restore from a full backup.
It means:
you have history daily backups;
one day, you had to restore from a full backup;
then from that day, all your history backup file groups would not be able restored backup into current database. unless, you restore to the point before that day.
All above is my test result. I hope it does not confuse you.
Edited by - xyz on 11/20/2003 07:21:24 AM
-
quote:
quote:
There's obviously no way to "play forward" without a subsequent log backup; where would the transactions possibly come from?--Jonathan
The transactions logs are exist cause I copy them to a temporary place before doing the Full Restore.
Recovery can only be done with either synchronised data and log files (i.e. from the same snapshot) or by restoring tran log backups, not the tran log files themselves, to a database backup that's been restored with norecovery. If the snapshot VDI is properly implemented you can restore the snapshot backup with norecovery and then apply subsequent tran log (or differential) backups from the "live" system.
--Jonathan
--Jonathan -
quote:
Recovery can only be done with either synchronised data and log files (i.e. from the same snapshot) or by restoring tran log backups, not the tran log files themselves, to a database backup that's been restored with norecovery. If the snapshot VDI is properly implemented you can restore the snapshot backup with norecovery and then apply subsequent tran log (or differential) backups from the "live" system.--Jonathan
The condition is there is no gap between logs.
Does the database has to be in the FULL mode all the time? No log truncation or simple mode happend?
-
quote:
quote:
Recovery can only be done with either synchronised data and log files (i.e. from the same snapshot) or by restoring tran log backups, not the tran log files themselves, to a database backup that's been restored with norecovery. If the snapshot VDI is properly implemented you can restore the snapshot backup with norecovery and then apply subsequent tran log (or differential) backups from the "live" system.--Jonathan
The condition is there is no gap between logs.
Does the database has to be in the FULL mode all the time? No log truncation or simple mode happend?
Not sure what you're asking; I was trying to specifically answer mail_db's questions.
You can apply unbroken chains of tran log backups to a full or differential restore. If the recovery model was set to Full for the entire period covering the last tran log backup you plan to restore, you may perform a point-in-time restore.
A tran log backup chain is broken if a tran log backup is bad or missing, or if the tran log was truncated other than by backing it up (including changing the recovery mode to Simple).
--Jonathan
--Jonathan -
Thanks; 🙂
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply