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
@myDbName
- 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.