Technical Article

Backup databases in an Availability Group

,

In an availability group it is possible to select whether a backup should be performed on a primary or secondary replica.

This script creates a list of databases that are in an availability group and reside on the primary replica.  Because these are candidates for a backup the script will be run.  When no candidates are present the script will not run.

Simply execute the script with an SQL Server Agent job at the required intervals.

CREATE PROCEDURE sp_AGDB_Backup
AS
IF (SELECT   COUNT(*) 
         FROM sys.availability_groups ag
       INNER JOIN sys.availability_databases_cluster adc
             ON (ag.group_id = adc.group_id)
       INNER JOIN sys.dm_hadr_availability_replica_states dhars
             ON (dhars.group_id = ag.group_id)
       WHERE dhars.role = 1) > 0
       BEGIN
             DECLARE @sql NVARCHAR(1000);
             DECLARE @path NVARCHAR(500);
             DECLARE @ext NVARCHAR(10);
             DECLARE @cmd NVARCHAR(1000);
             DECLARE @dbname NVARCHAR(100);
             DECLARE @role int;
 
             SET @sql = 'BACKUP DATABASE @dbname TO DISK = N''';
             SET @path = 'C:\Backup\';
             SET @ext = '.bak''' + ';';
     SET @role = 1; --Sets to backup on Primary
 
             DECLARE a CURSOR
             STATIC
             FOR
             SELECT adc.database_name
               FROM sys.availability_groups ag
             INNER JOIN sys.availability_databases_cluster adc
                    ON (ag.group_id = adc.group_id)
             INNER JOIN sys.dm_hadr_availability_replica_states dhars
                    ON (dhars.group_id = ag.group_id)
             WHERE dhars.role = @role
             ORDER BY adc.database_name ASC;
 
             OPEN a;
 
             FETCH NEXT FROM a
             INTO @dbname;
 
             WHILE @@fetch_status = 0
                    BEGIN
 
                           SET @cmd = REPLACE(@sql, '@dbname', QUOTENAME(@dbname)) 
+ @path + @dbname 
+ '_' 
+ REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ' ', '_'),':','-') 
+ @ext;
 
                           exec sp_executesql @cmd;
                           --PRINT @cmd;
 
 
                           FETCH NEXT FROM a INTO @dbname;  
                    END;
 
             CLOSE a;
             DEALLOCATE a;
 
       END;

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating