Full Backup, Differential Backup, and Log Backup
-
Hi,
Please help confirm this. I have backup schedule as below:
3 am Monday: full backup and log backup every hour from 5 am to 9 pm.
10 pm Monday: truncate the log
3 am Tuesday: differential backup and log backup every hour from 5 am to 9 pm.
10 pm Tuesday: truncate the log
3 am Wednesday: differential backup and log backup every hour from 5 am to 9 pm.
10 pm Wednesday: truncate the log
3 am Thursday: differential backup and log backup every hour from 5 am to 9 pm.
Now if some went wrong at 10:15 am on Thursday, I can restore the database up to 10:14 am by:
1. do a log backup
2. restore the database from Monday full backup with no recovery
3. restore the database from the Thursday differential backup with no recovery
4. restore the logs from log backups at 5 am, 6 am, 7 am, 8 am, 9 am , 10 am with no recovery
5 .restore the log from last log backup with stop at 10: 14 am and recovery
A guy told me that if I truncate the log every night the differential backup is useless. I am sure that truncating the log will not cause any problem when restore from differential backup because the restore does not need the previous log. I even tested and confirmed myself. However the guy made me nerve because it affect whole business if we can't restore a database. Can you confirm this?
Thanks.
-
I am curious as to why you truncate the log at 10:00 PM. Are there any jobs or user activity that occurs between 9:00 PM (the last transaction log backup) and 3:00 AM when you complete a Full Backup or Differential Backup?
-
No, there is no activity during this time. The reason I trucated the log is the log backups are appending into a single file for each day. Every day the first log backup runs with NOINIT staring a new backup file. Next log backups are appending into that file. Next day it will start another new log backup file. If I don't truncate the log, the log backup file sometimes grows to 50 GB.
-
My concern with your process is this; If you were attempting to restore to 10:00 AM Thursday due to a hardware failure and your Thursday morning Differential Backup file was corrupt. You could not restore to a time after 9:00 PM on Wednesday night due to the truncation of the log file at 10:00 PM Wednesday.
-
I appreciate your concern. However in my scenario all of my backups are assumed valid. My question was with the backups I have, am I able to restore to 10:14 am? Thx.
-
I just reread what you posted regarding your backup process. To be sure what is going on,
you use the same filename for your transaction log backup file each day. It is alsways called
dbtlog.trn for instance regardless of which day it is running on, Monday, Tuesday, etc, correct?
How are you doing the backups? Can you show us the code that you are using? There may be a
better way than the one you are currently using. Of course, that also depends on your
data recovery requirements. Perhaps what you are doing is sufficient for this application.
----
Never assume that your backup files will always be valid. In our jobs you should always assume that what can go wrong will and be prepared for it.
----
Your process as listed will work, except maybe the part about doing that last tlog backup. If you have totally lost the database log file, you may not get that step completed.
----
-
Hi Lynn,
Here it how I name the backups:
1. Monday: full backup to file Monday_Full.BAK, first log backup to file Monday_Log.BAK, next log backups appended to Monday_Log.BAK.
2. Tuesday: diff backup to file Tuesday_Diff.BAK, first log backup to file Tuesday_Log.BAK, next log backups appended to Tuesday_Log.BAK.
3. Wednesday: diff backup to file Wednesday_Diff.BAK, first log backup to file Wednesday_Log.BAK, next log backups appended to Wednesday_Log.BAK.
4. Thursday: diff backup to file Thursday_Diff.BAK, first log backup to file Thursday_Log.BAK, next log backups appended to Thursday_Log.BAK.
When I said somethings went wrong I meant someone deleted a whole table or updated whole table instead of certain rows. We have nothing after biz hours so we don't care if we can't restore the log after 10 pm. That was why I truncate the log nightly after last log backup. Of course we backup the files to tapes and send them to Iron Mountain. My question was the ability to restore the log from the given backups. If I think too far I would have rented a case to house my servers at DOD. Thx.
-
You could replace the 10:00 PM Truncate Log with a delete <Weekday>_Log.BAK prior to the first Transaction Log backup. As for preparing for EVERYTHING that can go wrong, there is of course a limit and that limit is how much money the company is willing to spend.
All I was trying to point out is that if you needed to restore to 10:00 AM on Thursday due to a hardware failure, and your Thursday morning differential backup file was corrupt, you would not be able to recover due to the Truncate Log done at 10:00 PM Wednesday.
-
I think you can be reasonably confident of being able to restore your database to any point in time between 3AM and 9PM. But there are some things in your description I don't follow.
NOINIT causes a backup file to be appended to the end of existing data in the backup set. It is the default option, so all your BACKUP LOG statements are using NOINIT whether you explicitly include it or not. It seems to me that you should use INIT for the first log backup at 5AM to start a new file.
Normally the log is truncated after every backup, so it is being truncated at 9PM. Unless you're running a huge index optimization maintenance plan between 9 and 10, I don't see what you accomplish by truncating it with no backup at 10PM. This doesn't seem likely, but maybe your log truncation turns the next NOINIT into INIT because you've broken the log chain?
I would try getting rid of the log truncation, and either deleting the old file every day before the first log backup or change the first log backup to use the INIT option.
-
Scott,
You are correct, I mistyped. It should be INT instead of NOINIT. Each day it starts the first backup WITH INIT, next backups are appended into the first backup using NOINIT. Same thing for other days.
The log is truncated at full backup. I agreed with this but the database is full backed up once a week, other days are differential backup. Is the log truncated after a differential backup?
Like I said the reason I truncated the log because the physical backup files sometimes grow up to 50 GB. All I need is able to restore from 3 am to 9 pm. After 9 pm I don't mind if the LSN is broken because the log is truncated.
Thanks.
-
It doesn't really matter if the log is truncated by the differential backup, if there is little activity overnight then the 9PM - 5AM activity is not growing the log backup file to 50GB. It sounds more like the INIT is not really clearing out the file. Books Online says there are some conditions that keep INIT from overwriting the previous backup set, maybe one of those applies to your situation. I would recommend deleting the old file and starting a new one every day to avoid the file growth problem, rather than the log truncation. You are correct that in your case no forseeable recovery scenario will be impacted by the log truncation, but it is something that all the documentation says you should avoid doing.
Can you really guarantee that no one in your company is going to come up with a scheduled job that makes some critical database entry at 2AM, perhaps without your knowledge? Some rogue Excel code cowboy perhaps?
-
Maybe you could try using "INIT SKIP" on your first backup of the day, which should tell it to skip checking the expiration date on the existing backup and force the overwrite. Or maybe add "RETAINDAYS=6" to all log backups, so they have all expired when the file is reused a week later. Or both. I don't have any experience with these options, feel free to experiment. The other setting that can foul up INIT is if the backup set name in the BACKUP statement disagrees with the backup media, but what would that have to do with log truncation?
I still don't know how log truncation makes this work. I was looking for something along the lines of an open transaction and thinking a CHECKPOINT before the first log backup might help, but I can't find anything in the documentation to back that up.
-
Hi,
to clarify: you are truncating the log by using "backup log with truncate_only", or are you using "backup log ..."?
truncate_only should only be used if you've got trouble and can't do a regular log backup - a normal "backup log" truncates the log anyway.
regarding differential and full backups - these are independent from the log backup, a differential backup needs only the full backup as basis. full + diff backup are to the log backups like a full backup at the point of time the differential backup was taken.
regards
karl
Best regards
karl
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply