Using SQL Native Backup URL, multiple databases to Azure Blob Storage
-
How to backup multiple database to Azure blob storage using native SQL Server Backup to URL option. I have used the Ola Backup scripts with success but will like another option. EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @BackupType = 'FULL', @Compress='Y', @copyonly ='Y', @Url='https://development.blob.core.windows.net/backups', @BlockSize=65536, @MaxTransferSize=4194304 --script to modify for multiple database DECLARE @name VARCHAR(100) -- database name Declare @url as Varchar(max) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup ;) DECLARE @fileDate VARCHAR(20) -- used for file name -- specify database backup directory e.g. 'https://development.blob.core.windows.net/backups' SET @url = 'https://development.blob.core.windows.net/backups' -- specify filename format SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','') DECLARE db_cursor CURSOR READ_ONLY FOR SELECT name FROM master.sys.databases WHERE name NOT IN ('master','model','msdb') -- exclude these databases AND state = 0 -- database is online AND is_in_standby = 0 -- database is not read only for log shipping OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @name + '_' + @fileDate + '.BAK' BACKUP DATABASE @name TO URL = @URL FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor -- Any suggestions that does not include having to execute the backup command for each database will be helpful
-
How to backup multiple database to Azure blob storage using native SQL Server Backup to URL option. I have used the Ola Backup scripts with success but will like another option. EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @BackupType = 'FULL', @Compress='Y', @copyonly ='Y', @Url='https://development.blob.core.windows.net/backups', @BlockSize=65536, @MaxTransferSize=4194304 --script to modify for multiple database DECLARE @name VARCHAR(100) -- database name Declare @url as Varchar(max) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup ;) DECLARE @fileDate VARCHAR(20) -- used for file name -- specify database backup directory e.g. 'https://development.blob.core.windows.net/backups' SET @url = 'https://development.blob.core.windows.net/backups' -- specify filename format SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','') DECLARE db_cursor CURSOR READ_ONLY FOR SELECT name FROM master.sys.databases WHERE name NOT IN ('master','model','msdb') -- exclude these databases AND state = 0 -- database is online AND is_in_standby = 0 -- database is not read only for log shipping OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @name + '_' + @fileDate + '.BAK' BACKUP DATABASE @name TO URL = @URL FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor -- Any suggestions that does not include having to execute the backup command for each database will be helpful
Myself I would first look for a commercial solution. Rolling your own requires dealing with all of the possible error scenarios and notifications that must occur when an error happens. But other than that there are two problems not one. So there should be two solutions. 1. Do the back up 2. Move the back up to the remote location.