My goal is to have one backup file per database per day, with 15 minute recovery objective. I am scheduling agent jobs as follows:
- Once a day at 00:00, create a full backup of each database specified
- Every 15 minutes, create a transaction log backup of each database specified
Ignoring all the code to iterate through the required databases, this is the core logic of part 1 above:
DECLARE @dbName nvarchar(50) = (SELECT DatabaseName FROM #DbsToBackup WHERE (Id = @current)); DECLARE @dbNameAndDate nvarchar(200) = @dateString + '_' + @dbName; DECLARE @dbSpecificContainerUrl nvarchar(MAX) = @containerUrl + @dbNameAndDate + '.bak'; DECLARE @verifyError nvarchar(200) = N'Verify failed. Backup information for database ' + @dbName + ' not found.'; BACKUP DATABASE @dbName TO URL = @dbSpecificContainerUrl WITH CREDENTIAL = @containerCredential ,NOINIT ,NAME = @dbNameAndDate ,COMPRESSION ,CHECKSUM ;
Then, the transaction log backup logic is as follows (same steps for grabbing the database name, date strings, etc.:
BACKUP LOG @dbName TO URL = @dbSpecificContainerUrl WITH CREDENTIAL = @containerCredential ,NOINIT ,NAME = @dbNameAndDate ,SKIP ,COMPRESSION ,CHECKSUM ;
Part 1 works on the first attempt, but part 2 (and any subsequent re-run of part 1) immediately fails with this:
A nonrecoverable I/O error occurred on file "https://.....Test.bak:" The file https://....Test.bak exists on the remote endpoint, and WITH FORMAT was not specified. Backup cannot proceed.. [SQLSTATE 42000] (Error 3271) BACKUP LOG is terminating abnormally. The step failed.
I get that the .bak file is already there, but I thought that
NOINIT forced an append which would add the transaction log data into the existing .bak file.
I'm probably missing something simple but can somebody please advise?