Transaction Log backup overwrites full backup on same media

Question

I have a database that is currently backed up every two hours using a SQL Agent job. This is a secondary backup, done in addition to the hosting provider's backup strategy. I created a procedure (stored within master) to perform the following actions on a scheduled basis in SQL Agent:

1. If the backup file doesn't exist, create it and perform a full (copy only) backup
2. If the backup file does exist, perform a transaction log backup

The code creates a backup file with a weekly date stamp (e.g. 2017_Week37_myDbName.bak), then drops and re-creates a new file each week.

The code is below:

ALTER PROC [dbo].[e4_backupDB]

    @myDbName nvarchar(50)

AS

    DECLARE @year nvarchar(4) = CAST(YEAR(GETDATE()) AS nvarchar(4));
    DECLARE @weeknumber nvarchar(2) = CAST(RIGHT('0' + RTRIM(DATEPART(wk, getdate())), 2) as nvarchar(2));
    DECLARE @backupFileName nvarchar(max) = @year + '_Week' + @weeknumber + '_' + @mydbname; -- '2013_Week37_myDbName'
    DECLARE @backupphysicalpath nvarchar(100) = N'C:\SqlScheduledBackups\';  
    DECLARE @fullpath nvarchar(150) = @backupphysicalpath + @backupFileName;  
    DECLARE @backupName nvarchar(200);

    IF NOT EXISTS (SELECT * FROM sys.backup_devices WHERE (name=@backupFileName))
        BEGIN

            PRINT 'Creating backup device and performing full backup...';

            -- create backup device and perform full backup
            EXEC master.dbo.sp_addumpdevice  @devtype=N'disk', @logicalname=@backupFileName, @physicalname=@fullpath;
            SET @backupname = @myDbName + '-Full';
            BACKUP DATABASE @mydbname TO  @backupFileName WITH COPY_ONLY, COMPRESSION, INIT, NAME=@backupname, STATS=10;

            -- delete the previous backup file
            DECLARE @previousBackupName nvarchar(200);
            SELECT
                  TOP 1 @previousBackupName = o.name
            FROM
                sys.backup_devices o
            WHERE
                (o.name <> @backupfilename) AND
                (o.name LIKE @year + '_Week_%_' + @mydbname)
            ORDER BY
                o.name ASC;

            IF @previousBackupName IS NOT NULL
                BEGIN
                    EXEC master.dbo.sp_dropdevice @logicalname = @previousBackupName, @delfile='delfile';
                END
        END
    ELSE
        BEGIN
            PRINT 'Performing transaction log backup...';
            SET @backupname = @myDbName + '-Transaction Log';
            BACKUP LOG @mydbname TO @backupFileName WITH COPY_ONLY, COMPRESSION, INIT,  NAME=@backupname, STATS=10;
        END
GO

After a few hours, if I open the backup media in SSMS and view its contents, I would expect to see:

1. A full database backup for @myDbName
2. The most recently-taken transaction log backup

Instead, it appears that the transaction log backup is overwriting the full backup. When I view the media contents, I only see the transaction log. I know that INIT might trigger this behaviour, but MSDN implies that if the NAME doesn't match, then it shouldn't get overwritten.

Why is the full backup getting replaced then please? I am trying to achieve a full backup once-per-week, then a transaction log backup every 15 minutes.

Answer 1

You are specifying INIT on both your FULL and transaction log backups.

If you want to 'append' the transaction log backups to the FULL backup, specify INIT on the FULL and NOINIT on the transaction log backups.

If you don't want your transaction log backups 'appended' to your FULL, I'd suggest generating a dynamic transaction log backup command 'appending' a timestamp onto the transaction log backup file name and using INIT.

Here is an 'example'

SET NOCOUNT ON 
declare @BackupTimeStamp varchar(25)
declare @BackupCommand varchar(max)
declare @VerifyCommand varchar(max)
declare @ServerName varchar(max) = (select @@SERVERNAME)

set @BackupTimeStamp = 
    right('00' + CONVERT(varchar(4),datepart(year,sysdatetime())),4) + '_' + 
    right('00' + CONVERT(varchar(2),datepart(month,sysdatetime())),2) + '_' + 
    right('00' + CONVERT(varchar(4),datepart(day,sysdatetime())),2) + '_' + 
    right('00' + CONVERT(varchar(4),datepart(hour,sysdatetime())),2) + 
    right('00' + CONVERT(varchar(4),datepart(minute,sysdatetime())),2) + 
    right('00' + CONVERT(varchar(4),datepart(second,sysdatetime())),2)

set @BackupCommand = 
'
BACKUP LOG [SCOPERATIONAL] TO  
DISK = ''\\FBPISILON01\DBBackups-SC\' + @ServerName + '\Xlogs\SCOPERATIONAL_backup_' + @BackupTimeStamp + '.bax'' ' + 
'WITH FORMAT, INIT,  NAME = ''SCOPERATIONAL_BACKUPS\SCOPERATIONAL_backup_' + @BackupTimeStamp + ''''
print @BackupCommand
exec (@BackupCommand)  --should probably be sp_executesql

Keep in mind, that EACH transaction log backup will continue to be larger UNTIL a non-COPY_ONLY backup is taken.

Additionally, should you decide to create transaction log backups that are not appended to your FULL backups, you'll need to create some sort of pruning/purging process to delete older transaction log backups.