MSSQL

How to take all sql databases differential backup in azure blob storage script

Topic Street

 How to take all sql databases backup in azure blob storage script


Below Script will help you take differential backup of all online databases to azure blob storage..

 
First create a SQL Server credential (If not created earlier), follow these steps.

  1. Connect to SQL Server Management Studio.

  2. Open a new query window and connect to the SQL Server instance of the database engine.

  3. In the new query window, paste the CREATE CREDENTIAL statement with the shared access signature.

/* Example:
USE master  
CREATE CREDENTIAL [https://[StorageAccountname].blob.core.windows.net/[containerName]] ---replace with actual storage account name and container including "[]"
WITH IDENTITY='SHARED ACCESS SIGNATURE'   
, SECRET = 'sharedaccesssignature' 
GO */

USE master  
CREATE CREDENTIAL [https://[StorageAccountname].blob.core.windows.net/[containerName]] ---replace with actual storage account name and container including "[]"
  -- this name must match the container path, start with https and must not contain a forward slash at the end
WITH IDENTITY='SHARED ACCESS SIGNATURE' 
  -- this is a mandatory string and should not be changed   
 , SECRET = 'sharedaccesssignature' 
   -- this is the shared access signature key that you obtained from azure account.   
GO
4. To see all available credentials, you can run the following statement in a query window connected to your instance:

SELECT * from sys.credentials
Then finally execute below script to take all differential backup:

DECLARE @name VARCHAR(max) -- database name 
DECLARE @path VARCHAR(max) -- azure blob storage path for backup files 
DECLARE @fileName VARCHAR(max)   -- filename for backup 
DECLARE @fileDate VARCHAR(max) -- used for file name 
Declare @SQL VARCHAR(max)


SET @path = N'https://[StorageAccountname].blob.core.windows.net/[containerName]/'  ---replace with actual storage account name and container including "[]"

set @SQL = ''

DECLARE db_cursor CURSOR FAST_FORWARD     FOR
SELECT name ,  @path + name + '_' + REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 100), '  ', '_'), ' ', '_'), '-', '_'), ':', '_') + '.bak' pathfile
FROM sys.databases 
WHERE  state_desc='ONLINE'
order by name

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  ,@fileName

WHILE @@FETCH_STATUS = 0  
BEGIN  

      
--print 'BACKUP DATABASE '+@name+' TO  URL = '''+@fileName+''' WITH  DIFFERENTIAL , 
--NOFORMAT, NOINIT,  NAME = ''Diff_'+@name+''', NOSKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10'
 
 
-- print '---' +@name +'---Start ------------------------'

exec('BACKUP DATABASE '+@name+' TO  URL = '''+@fileName+''' WITH  DIFFERENTIAL , 
NOFORMAT, NOINIT,  NAME = ''Diff_'+@name+''', NOSKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10' )
 
--  print '----------END---------------------'
 

      FETCH NEXT FROM db_cursor INTO @name ,@fileName
END 

CLOSE db_cursor  
DEALLOCATE db_cursor 

--print @SQL