Sql-server – Transaction Log backup overwrites full backup on same media

backupsql serversql-server-2012

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)


    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))

            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);
                  TOP 1 @previousBackupName = o.name
                sys.backup_devices o
                (o.name <> @backupfilename) AND
                (o.name LIKE @year + '_Week_%_' + @mydbname)
            ORDER BY
                o.name ASC;

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

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.

Best Answer

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'

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 = 
DISK = ''\\FBPISILON01\DBBackups-SC\' + @ServerName + '\Xlogs\SCOPERATIONAL_backup_' + @BackupTimeStamp + '.bax'' ' + 
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.