Using Backup to URL in SQL Server 2012 instance

  • Article

 

In this post, we would like to explain on how we can overcome the limitation of using the feature “backup to URL” in Maintenance Plan wizard in SQL 2012

 

Symptoms

We cannot take backups from maintenance plans using backup to URL to Azure Blob Storage using SQL 2012 Engine instance with 2012/2014/2016 SSMS.

 

Error: -

With SQL 2012, We do not have the option to backup to URL. With 2014/2016 SSMS we do have the option to backup to URL in maintenance plans but that would execute with success but not back up files on the blob.

The Maintenance plan gets executed successfully but does not give any error.

Cause

Maintenance plans in SQL 2012 does not have the option to use “Backup to URL” Feature. If we install SSMS 2014/2016 on the 2012 Engine instance, we will see the option but the backup will still not work to the azure blob.
By default, the backups will go to the disk even if we select the “backup to url” option.

Resolution

  • I have written a T-SQL script to do the same. We can create an Agent Job and set up a schedule to back this up (Note: We can also you TSQL step and use the below script if we need to use Maintenance plan)
  • We need to first create a credential in SQL Server which will authenticate to the blob storage to save the files. Use the below script for the same:

            Create a credential for the blob storage to be used during backup:

--Identity is the storage account name, whereas secret is the access key to the storage account

IF NOT EXISTS(SELECT * FROM sys.credentials

 WHERE credential_identity = ' bkuptourl')

CREATE CREDENTIAL bkuptourl WITH IDENTITY = '2012bckup'

 ,SECRET = 'TvAra31QMlCfBRpTyX9/1wMJglNFdGW+9NX5OOVp5uU3kbMeC18Pq8Rt7iJ552oU2tGM6rzmKOkiUcaZxwDtwQ==';

 

 

Navigate to SQL Jobs and create a new Job with T-SQL, set up a schedule and add the below script to back up all the databases except tempdb:

 

DECLARE @DB_name VARCHAR(50) -- database name 

DECLARE @BackupLoc VARCHAR(256) -- path for backup files 

DECLARE @BackupfileName VARCHAR(256) -- filename for backup 

DECLARE @fileDate VARCHAR(20) -- used for file name

-- specify database backup container location

SET @BackupLoc = 'https://SQLServerBackup.blob.core.windows.net/Prod/'

set @fileDate= replace(replace(convert(nvarchar(50),getdate()),' ','_'),':','_')

DECLARE db_cursor CURSOR FOR

SELECT name FROM master.sys.databases WHERE database_id <>2 and state=0

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @DB_name  

WHILE @@FETCH_STATUS = 0  

BEGIN

       SET @BackupfileName = @BackupLoc + @DB_name + '_' + @fileDate + '.BAK'

       BACKUP DATABASE @DB_name TO URL = @BackupfileName WITH CREDENTIAL = 'bkuptourl',COMPRESSION

          print 'BACKUP DATABASE '+@DB_name+ ' TO URL ='''+ @BackupfileName +''' WITH CREDENTIAL = ''bkuptourl'',COMPRESSION '

       FETCH NEXT FROM db_cursor INTO @DB_name  

END

CLOSE db_cursor  

DEALLOCATE db_cursor

 

 

Sample Output

 

clip_image001

 

 

Written by – Ujjwal Patel, Support Engineer.
Reviewed by – Raghavendra Srinivasan, Sr. Support Engineer.