Scheduling of Tran Logs and Full Backups...
-
I want to make sure that I can restore up to the minute with any database on my server.
Currently I have tran logs scheduled every hour on top of the hour starting at 1am and going to 11pm. I also have the full backup starting at 12am every day. I thought I was covered but today after looking at this again, I am thinking that I will have a problem restoring data from the last tran log at 11pm and the full DB job at 12am. Am I correct in my thinking?
To correct this wouldn't I have to run a tran log backup just moments before my full backup so that I close this window?
Any thoughts?
Thanks
SJ
-
You should be able to restore anytime up to 12 midnight using the 11pm (and prior) transaction logs, plus a portion of the 1am tran log backup. The full backup shouldn't be required for such a point-in-time recovery.
PS. No liability accepted.
Cheers,
- Mark
Cheers,
- Mark -
I have always scheduled my transaction log backups to not run during my full or differential database backups.
I was asked today if the log backups could be scheduled to run during the full and differential backups of the database.
If log backups can be done during a full or differential backup
would they apply to the current full/differential backup or the previous full/differential backup?
Thanks.
-
As far as I know logical log backups do not refer to a complete or differential backup at all. They just continue from the last logical log.
After you restore any complete backup (the latest or one a year old) you can restore logical logs from the last before that backup was taken. There is no window that cannot be restored. (As I am a bit paranoid about the backup methods on our network and my understanding of Microsoft documentation 😉 , I have tested this.)
lharra,
You can do the logical log backups at the same time as the full/differential backup. It is just heavy on the I/O and CPU use and because the the logical logs backups do not take into account the full backups there is nog good reason to do it.
Joachim.
-
There is no reason to stop transaction log backups while a full backup is running, but, the transaction log backups will stall while a full or differential backup is running.
-
I had a similar concern....
Backing up the database does not flush the transaction log and thus does not break the chain of transaction log files?
Basically, it sounds like people are saying that if transaction logs are done hourly, a backup file combined with transaction log files dated after the backup can restore to a point in time- whether or not there was another more recent full backup done in the meantime. Correct?
Creating more than one (full or differential ) backup file per day does not break the chain of hourly transaction logs. Right?
We have hourly transaction logs with a nightly full native sql backup. I wanted to do a sql lite speed compressed backup during the day. Just wondered if I would still be able to use our native nightly backup combined with hourly transaction logs for a restore to point in time, or if I would have to use the most recent backup which would be the compressed backup plus transaction logs since the compressed backup.
Thanks,
Amy
-
According to the Books OnLine (BOL), the transaction log backups apply to the last full backup.
Refer to BOL, use the Index tab, enter
transaction logs
double click on 'backing up'
Select the option for Transaction Log Backups - Administering SQL Server.
Then, scroll down to the paragraph 'Using Transaction Log Backups with Database Backups'.
-SQLBill
-
Amy,
As Bill stated transaction log backups apply to the last full backup. So if you do a full SQL Litespeed backup in the middle of the day the next log backup would go from there. On my systems we have a full backup done every night and then a differential backup done at noon. On top of that every single transaction uses the "With Mark" option so that I can restore the database to that point in time. One caveat to that is that I had to remove a trigger in the msdb database that cleans out the logmarkhistory table so that we could easily see the marked log entries to restore to. We've been using this system for the last 18 months and it works great.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
quote:
I had a similar concern....Backing up the database does not flush the transaction log and thus does not break the chain of transaction log files?
That's correct.
quote:
Basically, it sounds like people are saying that if transaction logs are done hourly, a backup file combined with transaction log files dated after the backup can restore to a point in time- whether or not there was another more recent full backup done in the meantime. Correct?Yes.
quote:
Creating more than one (full or differential ) backup file per day does not break the chain of hourly transaction logs. Right?That's right.
quote:
We have hourly transaction logs with a nightly full native sql backup. I wanted to do a sql lite speed compressed backup during the day. Just wondered if I would still be able to use our native nightly backup combined with hourly transaction logs for a restore to point in time, or if I would have to use the most recent backup which would be the compressed backup plus transaction logs since the compressed backup.You can restore any full or differential backup and then apply an unbroken sequence of tran log backups if they extend back to before the backup you restored, whether or not there were subsequent full or differential backups.
Sorry to add to this thread, but I found some of these answers confusing.
--Jonathan
--Jonathan
Accepting all invites
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply