Does taking a log backup, truncating the log, and then deleting the tlog backup break the LSN chain?

Question

We have avamar tool taking backups in my environment. The backup plan avamar does is only taking trans backups once a day. I am convincing them to take the trans backup more frequent. But in the meanwhile, I have to deal with large log sizes because of this infrequent trans backups. Hence to reduce the log sizes, I am taking a quick trans backup followed by a truncate log size. Now I want to know if doing that breaks the log chain for avamar and it forces it to take full back up instead. Remember, I am deleting the trans backup file immediately after I truncate the log sizes. Does the fact that I deleted the trans backup file breaks the log chain for Avamar? Any input is appreciated.

Answer 1

If the avamar tool uses its own tracking method to know what log backup files it can restore, then it will fail.

If it uses SQL internal tables to know what log backup it needs, that is good, but if the file has been deleted, then it will fail.

Either way it will fail since you are deleting the files...so don't :)

Answer 2

Reading your question makes me wonder if you've actually attempted to restore these databases as proof of your backup strategy (either by naming the databases something different on the current server or restoring them to another server). You say Avamar handles the whole backup and recovery and yet, you're doing things (deleting transaction log backups) behind its back. You also say that Avamar only takes transaction log backups once per day. By deleting the transaction log backup file, you just made it impossible to recover to any point after that (unless you are taking additional FULL or DIFF backups). When was the last time a FULL or DIFF backup was taken? Even if you weren't deleting the transaction log backups you're taking, it would take you a VERY long time to restore your latest FULL backup (assuming you still have it) and DIFF and ALL of the transaction logs to get you close to where you want to be in a recovery situation.

The absolute FIRST thing you need to do is identify your Recovery Point Objective (RPO) and Recovery Time Objective (RTO).

RPO basically is, how much data could we 'possibly' lose should a bad problem happen? 5 minutes, 1 hour, 1 day, NONE?

RTO basically is the maximum time allowed to get things back to 'normal' or at least functioning. You would need to take into consideration small problems (server went down) to big problems (the building just exploded).

Since you are basically throwing away the transaction log backups you're taking, you are obviously not interested in being able to restore to a given point in time. If that is the case, you should just change your recovery model to SIMPLE and then you won't need to worry about taking your transaction log backups. Just bear in mind that under SIMPLE recovery, you will only be able to recover back to the last FULL or FULL/DIFF.

Check out The 9 Letters That Get DBAs Fired.

RPO, RTO and CYA

Right now, all of your users believe your RPO and RTO is zero. They believe you’ve got everything under control, and that when disaster strikes, you’re going to instantly bring up another server with no data loss whatsoever.

Answer 3

I am taking a quick trans backup followed by a truncate log size.

I believe you want to say taking transaction log backup and shrinking ?, If not in full recovery model transaction log backup would truncate transaction logs subject to few restrictions. And please stop shrinking log file daily this is big performance killer. And shrinking transaction logs DO NOT break the log chain so you are safe.

Remember, I am deleting the trans backup file immediately after I truncate the log sizes.

Other blunder you are doing is deleting the files after backup, how are you going to recover database if disaster strikes. Please stop that as well. Please define proper RPO and RTO and make a backup retention policy after which you may delete the files which exceeds the retention date.

And NO taking log backup and deleting the files do not break log chain but you actually lost the ability to do point in time recovery as files which stored backup is deleted

Further to our conversation you asked whether taking manual backup would break the log chain and answer it yes. Even though you take and delete the files the changes are done to the LSN when you take manual backup.

Answer 4

Does the fact that I deleted the trans backup file breaks the log chain for Avamar?

This script will query msdb.dbo.backupset and create a set of restore commands. If you delete any of the transaction log files listed in the restore commands, then the backup chain is broken.

/* Generate the script for restoring a database and the transaction logs */

DECLARE @databaseName sysname 
DECLARE @backupStartDate datetime 
DECLARE @backup_set_id_start INT 
DECLARE @backup_set_id_end INT 

-- set database to be used 
SET @databaseName = 'YourDatabaseNameHere' 

SELECT @backup_set_id_start = MAX(backup_set_id) 
FROM msdb.dbo.backupset 
WHERE database_name = @databaseName AND type = 'D' 


IF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999 

-- Last Full and any Differentials
SELECT backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = ''' 
+ mf.physical_device_name + ''' WITH NORECOVERY' 
FROM msdb.dbo.backupset b
INNER JOIN msdb.dbo.backupmediafamily mf 
ON b.media_set_id = mf.media_set_id 
WHERE B.type IN ('D', 'I')
AND b.database_name = @databaseName 
AND b.backup_set_id >= @backup_set_id_start AND b.backup_set_id < @backup_set_id_end 
UNION 
-- Log Backups
SELECT backup_set_id, 'RESTORE LOG ' + @databaseName + ' FROM DISK = ''' 
+ mf.physical_device_name + ''' WITH NORECOVERY' 
FROM msdb.dbo.backupset b
INNER JOIN msdb.dbo.backupmediafamily mf 
ON b.media_set_id = mf.media_set_id 
WHERE b.database_name = @databaseName 
AND b.backup_set_id >= @backup_set_id_start AND b.backup_set_id < @backup_set_id_end 
AND b.type = 'L' 
UNION 
SELECT 999999999 AS backup_set_id, '--RESTORE DATABASE ' + @databaseName + ' WITH RECOVERY' 
ORDER BY backup_set_id

Replace 'YourDatabaseNameHere' with the name of your database.

For those databases that use the FULL recovery model it is suggested to take frequent LOG backups.

I generally use 5-15 minute increments for LOG backups depending on how busy the database is. Here is a TechNet Article on it: Working with Transaction Log Backups

Answer 5

(Update: My answer below only applies if Avamar uses SQL Server's backup mechanisms. SQL_Underworld's comment on your question led me to look a little at it, and it appears it may use a completely separate mechanism for differential backups [likely not log, and almost certainly not daily full].
So it's actually a possibility that Avamar will do it's job even if you set your database to Simple recovery model, which would solve your log growth issues. But you would need to confirm with Avamar that their software doesn't rely on a specific recovery model.
Or if you can't get confirmation on that, you can probably at least get confirmation as to whether or not it needs the log in order to take backups. If not, then you can take log backups as often as you need in order free log space for reuse. But Shanky is correct in that you should not normally shrink the file after log backup, as that will just make your system work harder than needed for regrowing the file.)

Original answer:

What you're doing is certainly breaking the log chain, meaning that if you have to restore, you will lose all data changes since the last log backup Avamar took before the first manual log backup you took after the last full/differential backup Avamar took. (Sorry, can't think of a clearer way to describe it accurately.)

As for whether or not Avamar will detect a broken log chain in its storage location and take a full backup, that's something only they can answer with certainty.

But assuming Avamar uses standard full and log backup methods, the following query (from Get last full backup and transaction log backup for each database) can tell you when the last full/differential backup was taken.

;with backup_cte as
(
    select
        database_name,
        backup_type =
            case type
                when 'D' then 'database'
                when 'L' then 'log'
                when 'I' then 'differential'
                else 'other'
            end,
        backup_finish_date,
        rownum = 
            row_number() over
            (
                partition by database_name, type 
                order by backup_finish_date desc
            )
    from msdb.dbo.backupset
)
select
    database_name,
    backup_type,
    backup_finish_date
from backup_cte
where rownum = 1
order by database_name;