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