Sample Header Ad - 728x90

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