June 27, 2007 at 5:14 pm
Database in full recovery model, backs up with overwrite every night at 9pm. Transaction log for database backs up with overwrite every night at 9:45pm.
BOL states: "During a full database or differential backup, Microsoft® SQL Server™ backs up enough of the transaction log to produce a consistent database for when the database is restored. Only a full database backup can be performed on the master database."
SO, does that mean that the 9:45 tran log backup contains only records from 9pm to 9:45, or does it have records since 9:45 the previous night?
I've always backed up my logs before backing up my databases: that way, to my thinking, I can apply the log to the previous day's data backup if that night's data backup should be bad.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
June 27, 2007 at 8:52 pm
Hi Wayne,
yes you are absolutely right......the tran logs contains only the transactional changes made since the full backup in ur case after 9;00 full backup 1st trn is @ 9:45 so the 45min changes will be there.... if there is a 2nd tran log backup @10:30 it consist of all the changes made from 9:45 till 10:30.....and so on....if a full backup occurs it will nullify the tran log sequence....so as BOL states inorder to have a consistent state of ur db in case of any corruptionss to your db u need to hav as much tran log backups to recover to original state b4 corruption occured to ur db......
[font="Verdana"]- Deepak[/font]
June 27, 2007 at 10:52 pm
To add more this chain, Read more on Differentialchange map and bulk change map in BOL to know how sql server backs up the database and log files.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 28, 2007 at 8:22 am
The log backups will backup all transactions since the last log backup, even if a full backup has been run in between. If you need to restore the DB after the log backup, you can restore from the full, and then apply the log backup. It will ignore any transactions in the log backup from before the full backup. See the page in BOL titled "Transaction Log Backups" under Administering SQL Server. Scroll down to the section titled "Creating a Sequence of Transaction Log Backups" for a good example of the timing.
Cary
June 28, 2007 at 2:28 pm
Do NOT overwrite every transaction log backup or they are really worthless for restoring. You need every transaction log since the full backup to be able to do the restore. If you are using a maintenance plan, have it keep at least a day's worth of backups (delete older than 1 day is okay). If you are scripting it, create two jobs for transaction logs. One should run shortly after the full backup, it should use the with init option and only run once a day. The second job should run several times during the day and use WITH NOINIT - this one will run every hour, 1/2 hour, whatever...
Doing a differential backup makes the whole thing easier, as all differentials have the changes since the last full backup.
-SQLBill
June 28, 2007 at 3:00 pm
Thanks, Cary! I guess it's been a while since I reviewed the logic behind backups. For some reason I was thinking that a full data backup would invalidate a subsequent transaction log backup, but BOL clearly states otherwise.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply