Backup files are lost while backing up to Azure URL from SQL Server 2016
3
votes
2
answers
296
views
I have a job script like this which should backup SQL Server 2016 dbs to Azure blob storage with storage key credential. Usually it works fine, but sometimes a few backups are lost (I mean there is no backup file on the storage account) and I do not get any kind or job failure or errors on job log file. Dbs are just skipped. I have noticed that when backup job overlaps maintenance procedures this happens quite often when I have changed times it happened once a month or less. But I am not sure that maintenance(index maint, dbcc, stats update) is the reason for this anomaly.
I would like to know whether had you any prior experience like this and may know what is the core reason?
The interesting part is that I have restore verifyonly which also just skips databases. It does not try to restore those dbs that's why I do not get fail errors, just skips
DECLARE @dbname sysname
DECLARE @path nvarchar(120)
DECLARE @credential sysname = 'BackupStorageCredential'
DECLARE @date nvarchar(250) = CAST( GETDATE() AS Date )
SET @path = N'[my_storage_url]'
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases
WHERE name IN ('db1','db2','db3')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @query_backupToAzBLOB NVARCHAR(max)
DECLARE @query_verify NVARCHAR(max)
SET @query_backupTOAzBLOB = 'BACKUP DATABASE [' + @dbname + '] TO URL =''' + @path + @dbname + '/' + @dbname + '_' + @date +'.bak''
WITH CREDENTIAL = ''' + @credential + ''',NOFORMAT, NOINIT, NAME =''' + @dbname + ''',
NOSKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM'
EXEC (@query_backupTOAzBLOB)
SET @query_verify = 'RESTORE VERIFYONLY FROM URL =''' + @path + @dbname + '/' + @dbname + '_' + @date +'.bak''
WITH CREDENTIAL = ''' + @credential + ''', FILE = 1, NOUNLOAD, STATS = 5'
EXEC(@query_verify)
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
Asked by igelr
(2162 rep)
Dec 29, 2021, 08:45 AM
Last activity: Dec 29, 2021, 08:42 PM
Last activity: Dec 29, 2021, 08:42 PM