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:
- 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 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:
- A full database backup for
- 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.