2

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.

3
  • 1
    There is no reliable way to build a log chain with COPY_ONLY log backups. Every time your hosting provider takes a non-COPY_ONLY log backup it will break the "log chain" for your COPY_ONLY log backups. This is because the transaction log records that occur between your last COPY_ONLY log backup and the non-COPY-ONLY log backup will be missing. Sep 11, 2017 at 16:47
  • I'll second @DavidBrowne-Microsoft: Your transaction logs are going to be completely useless as is. Only you or your hosting provider can take transaction log backups and have them re reliable. Is there any reason you don't trust your hosting provider to do this for you?
    – AMtwo
    Sep 11, 2017 at 19:08
  • The provider takes a VSS backup at 02:00am each day using R1Soft. However, they have been unable to clarify if the log chain is affected by this. I am waiting for an update but in the meantime am trying to put a non destructive mechanism in place
    – EvilDr
    Sep 11, 2017 at 20:11

1 Answer 1

4

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.