How To Backup Only Availability Group Databases in an Instance

dbtut October 14, 2018 MSSQL

Sometimes we need to take backups on an AG basis. Finding databases that are included in individual AG and getting their individual backups will make our job more difficult.

With the help of the following script you can get the backup of the databases that are included in an AG using Cursor.

You must write the name of your availability group in place of AGNAME in Scrpt.

In Script, it takes backups into “C:\Backup” folder. Here you can change the folder to receive backup.

DECLARE @name VARCHAR(50) --database name
DECLARE @path VARCHAR(256) -- the path for backup files 
DECLARE @fileName VARCHAR(256) --the file name for backup
DECLARE @fileDate VARCHAR(20)  --backupdate
SET @path = 'C:\Backup' 
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR 

SELECT DISTINCT
dbcs.database_name AS name
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
   ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
WHERE ISNULL(arstates.role, 3) = 1 AND ISNULL(dbcs.is_database_joined, 0) = 1 AND AG.name='AGNAME'
ORDER BY  dbcs.database_name

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  
WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
       BACKUP DATABASE @name TO DISK = @fileName  WITH COMPRESSION
       FETCH NEXT FROM db_cursor INTO @name  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor

DECLARE @name VARCHAR(50) --database name

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

DECLARE @fileName VARCHAR(256) --the file name for backup

DECLARE @fileDate VARCHAR(20) --backupdate

SET @path = 'C:\Backup'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR

SELECT DISTINCT

dbcs.database_name AS name

FROM master.sys.availability_groups AS AG

LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates

ON AG.group_id = agstates.group_id

INNER JOIN master.sys.availability_replicas AS AR

ON AG.group_id = AR.group_id

INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates

ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1

INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs

ON arstates.replica_id = dbcs.replica_id

LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs

ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id

WHERE ISNULL(arstates.role, 3) = 1 AND ISNULL(dbcs.is_database_joined, 0) = 1 AND AG.name='AGNAME'

ORDER BY dbcs.database_name

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

BACKUP DATABASE @name TO DISK = @fileName WITH COMPRESSION

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux

How To Backup Only Availability Group Databases in an Instance

About dbtut

Leave a Reply Cancel reply