jasona.work (1/27/2012)
Megistal (1/27/2012)
First nice article!Second, must I understand that the only way in SQL 2008 and Up to break the log chain is to switch to simple recovery model?
Pre-SQL Server 2008, there were a couple of command, namely BACKUP LOG WITH NO_LOG or BACKUP LOG WITH TRUNCATE_ONLY (they are functionally equivalent) that, when issued, would force a log file truncation and so break the log chain
Ty
My understanding is that the log chain starts at the last full DB backup. So if someone runs a full backup of the DB in the middle of your backup cycle, then deletes that full backup, your log chain is broken.
If there is a need to run a DB backup, but you don't want your chain to be broken, running a COPY ONLY DB backup will work.
BACKUP DATABASE {DBName} TO DISK = '{Backup location}' WITH COPY_ONLY
Doing a full backup in the middle of a chain won't break it. You can still take the previous full and apply all logs up to the current point if no log is missing.
The log is filled out when a transaction log backup is being taken not by a full.
At least this is a behavior in 2008 and I would guess it's the same in 2005.