November 18, 2013 at 10:40 am
Scenario:
SQL Server 2008,
Database Full recovery model,
full backup every night, taking approx. 2.5 hours to complete.
log backups every half-hour
I know that a full backup should be run immediately after a restoration, but my question is if a point-in-time recovery is possible between the completion of database restoration and completion of next full backup, which may take some time to complete.
I assume it is not, but have not found any confirmation of that and would like to know if what I have been telling users is correct. I have stated that any work done on the database after the restoration has completed but before full backup completion could be lost in the event of another problem, even though logs will continue to be backed up during that period, and a differential backup would be of no value either.
Is this correct?
Thanks for confirmation or correction.
November 18, 2013 at 1:47 pm
1.Restore the latest full with NORECOVERY option
2.Restore all tlog backups with NORECOVERY option, except for the last tlog backup which would be restored with the RECOVERY option
November 18, 2013 at 1:53 pm
Donald Sawford (11/18/2013)
I know that a full backup should be run immediately after a restoration
That shouldn't really be necessary, assuming you still have the backups you used to do the restore.
If you do a restore to a point-in-time, particularly with multiple log files, it can make sense to do a full backup after that to avoid having to repeat the entire recovery process if something goes wrong. But, a differential backup should still work at that point too, and might save a lot of time. [Also, there are techniques you can use to see roughly what % of the blocks have changed to determine if a differential backup is worthwhile.]
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 18, 2013 at 2:21 pm
If I understand your question you are asking if you have finished a restore, but a full (or diff) hasn't completed yet can you restore the database to a point in time after the last restore? Is that correct?
The answer is it depends. It depends on what caused the backup not to complete. Once you have finished the restore you can take TLog backups that will allow you to restore to a point in time (all backups you used plus the new Tlog backups) while you are starting a full backup. If something happened to your system, but you were still able to take a tail-of-log backup you could recover right to the point of failure. Does that answer your question?
November 18, 2013 at 3:58 pm
Thanks for all of the input,
To spell out the specific example:
Fri. evening after normal work is done, I do a diff backup so that with the last full backup, I have a restore point.
An archiving process is allowed to run over the weekend that is modifying data. During this process, full backups and log backups continue to run.
Sunday night, it is determined that the process is not going to complete successfully, so we must roll back to Fri. evening before the process started.
It was felt that if it didn't complete entirely, the integrity of the data would be at risk for normal work to resume without rolling back.
So, I restored the last full backup prior to Fri. evening and the diff that I ran at the end of day, and it is up and running ready for production to resume work at 8:00 am Monday.
However, my question is whether I still have the ability to do a point-in-time restore on that database from the time they resume using it at 8:00 until the next full backup completes, at least a couple of hours later.
Suppose that the database goes down at 10:00 am and a new full backup has not entirely completed.
I know that I can go back to Fri. night with the same full and diff restores that I did earlier and lose the mornings work, but am I correct in assuming that I cannot apply the logs from Mon. morning to save the work that was performed between 8:00 and 10:00?
Or is it possible do the Fri. night restore again but this time leaving NORECOVERY on the diff and continue restoring the logs that backed up between 8:00 and 10:00 Mon. to the desired point-in-time?
November 18, 2013 at 4:24 pm
Scott,
So after doing the restore, all log and diff backups that run after the database is brought back up can be added to the initial backups used for second the restore to maintain point-in-time?
I think that makes sense now that I think about it.
Thanks again to everyone that responded.
November 18, 2013 at 4:51 pm
I think so, but I couldn't swear to it. I think the differential bitmaps are restored as-is with the db, and not reset, but I'm not 100% sure. That's why I mentioned the code to check it. Or you just do a base restore, then a differential restore, then run a differential backup and see if it was the same size as the last differential :-).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 18, 2013 at 8:42 pm
Donald as long as you don't break the back chain you can restore to a point in time. So, once you restore Friday's backup you can continue that chain by continuing to take Tlog backups even while you are taking your full backup. Once your Full finishes you have a new backup chain.
So restore Friday's backup continue taking log backups and start your full backup. If for some reason your system crashes before the full finishes you can restore Friday's full again and all log backups that you took after you restored the first time.
November 18, 2013 at 10:54 pm
Full and differential backups do not break the log chain of tlog backups.
As long as you don't do anything to break the tlog chain, when you restore your full and differential backups and start taking tlog backups again, you will still be able to do a point in time restore if necessary before the next full backup is taken simply be starting with the same full and differential backups you restored from.
November 19, 2013 at 2:14 am
You could use last years full backup 🙂 provided you have all of the transaction logs since then so it doesn't matter if the latest full backup hasn't completed yet.
You can use the previous full backup, differential if you have one and transaction logs to do point in time.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply