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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
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 |
