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:
- If the backup file doesn't exist create it and perform a full (copy only) backup
- 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
Best Answer
You don't indicate how often your provider issues
BACKUP LOG
commands (which I assume are notCOPY_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.
BACKUP LOG COPY_ONLY
runs and doesn't really backup anything because the log has just been truncated by the normal log backup.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.BACKUP LOG COPY_ONLY
runs and backs up everything since 8:00am (because that's when the log was last truncated) - now 2gb.BACKUP LOG COPY_ONLY
runs and backs up everything since 8:00am (because that's when the log was last truncated) - now 3gb.BACKUP LOG COPY_ONLY
runs and backs up everything since 8:00am (because that's when the log was last truncated) - now 4gbYou are also 'appending' this data to your existing backup because you are using
NOINIT
.