0

I have a database that is currently backed up every two hours. 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 currently scheduled to run every two hours, which for my level of understanding meant I would have a maximum of two hours data loss in the event of failure. The initial backup file created in step 1 is 230MB, which obviously increases every two hours as the transaction log backup is added. Each week, the file reaches a size of around 7GB, before being deleted by the procedure.

I then changed the schedule to run every 15 minutes, but noticed that the backup file went over 230GB - a gigantic increase. There may be a bug (which I cannot figure), but I'm wondering why this would happen? I thought that the transaction log backup would only contain the transactions backed up since the last backup, so regardless of the frequency, the backup file would always be a similar size before it was deleted. I should clarify that at no point was the database used any more frequently than normal. Basically, I am trying to create differential-style backups after the initial full backup.

CREATE PROC [dbo].[BackupMyDB]

    @myDbName nvarchar(50)

AS

    DECLARE @year nvarchar(4) = CAST(YEAR(GETDATE()) AS nvarchar);
    DECLARE @weekNumber nvarchar(2) = CAST(RIGHT('0' + RTRIM(DATEPART(wk, getdate())), 2) as nvarchar);
    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

            -- create backup device and perform full backup
            EXEC master.dbo.sp_addumpdevice  @devtype=N'disk', @logicalname=@backupFileName, @physicalname=@fullpath;
            SET @backupname = @myDbName + '-Full Database Backup';
            BACKUP DATABASE @mydbname TO  @backupFileName WITH COPY_ONLY, COMPRESSION, RETAINDAYS=45, NOFORMAT, NOINIT,  NAME=@backupname, SKIP, NOREWIND, NOUNLOAD, 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
            SET @backupname = @myDbName + '-Transaction Log Backup';
            BACKUP LOG @mydbname TO  @backupFileName WITH COPY_ONLY, COMPRESSION, NOFORMAT, NOINIT,  NAME=@backupname, SKIP, NOREWIND, NOUNLOAD, STATS=10;
        END
GO
  • 1
    EvilDr, If you need a better backup solution then I personally recommend using Ola Hallengren, Then enable every 1mins log backup, everyday 1 full backup. The reason why it grows to 230Gb is it might be a huge transaction or the previous backups have not happened. – Bhuvanesh Sep 8 '17 at 12:36
  • How often does the provider issue BACKUP LOG commands. Your log backups are COPY_ONLY, so each run of your process will back up everything in the log since the last NON-COPY_ONLY backup. – Scott Hodgin Sep 8 '17 at 12:45
3

You don't indicate how often your provider issues BACKUP LOG commands (which I assume are not COPY_ONLY), but here is my guess at to what is happening.

Let's assume a 'regular' transaction log backup has occurred at 8am which truncates the log. Also, let's assume that 1gb of transaction log is being created every 15 minutes.

  • 8:00am - Your BACKUP LOG COPY_ONLY runs and doesn't really backup anything because the log has just been truncated by the normal log backup.
  • 8:15am - Your BACKUP LOG COPY_ONLY runs and backs up up everything since 8:00am (because that's when the log was last truncated). Let's say 1gb.
  • 8:30am - Your BACKUP LOG COPY_ONLY runs and backs up everything since 8:00am (because that's when the log was last truncated) - now 2gb.
  • 8:45am - Your BACKUP LOG COPY_ONLY runs and backs up everything since 8:00am (because that's when the log was last truncated) - now 3gb.
  • 9:00am - Your BACKUP LOG COPY_ONLY runs and backs up everything since 8:00am (because that's when the log was last truncated) - now 4gb

You are also 'appending' this data to your existing backup because you are using NOINIT.

  • 1
    NOINIT for the win... or rather loss (of disk space) in this scenario... – John Eisbrener Sep 8 '17 at 13:27
  • Fantastic explanation - thank you. Yes indeed, NOINIT was the cause. – EvilDr Sep 9 '17 at 5:30
  • Still having some issues. Would appreciate any attention you might be able to spare here please... – EvilDr Sep 11 '17 at 10:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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