Technical Article

Taking backup of the databases to the Active server alone

,

You may use the script if you have any active stand by servers.

SET NOCOUNT ON;

IF SERVERPROPERTY ('IsHadrEnabled') = 1
	BEGIN
		DECLARE @ActiveReplica SYSNAME;
		DECLARE @ThisServer SYSNAME;

		SELECT @ActiveReplica = primary_replica 
		FROM sys.dm_hadr_availability_group_states;

		SELECT @ThisServer = @@SERVERNAME;

		IF @ThisServer = @ActiveReplica
			BEGIN
			-------------------------------------------------------------------------------------------
			-- Write the backup blog using cursor or while loop to loop through all databases.
			-------------------------------------------------------------------------------------------
			END
		ELSE
			BEGIN
				PRINT '-----------------------------------------------------';
				PRINT 'The Active Server for this AG is Currently ' + @ActiveReplica;
				PRINT '-----------------------------------------------------';
				THROW 60000, 'No backup will be taken since this is not the active server',1;
			END
	END
ELSE
	BEGIN
		;THROW 60000, 'This server is not part of an Availability Group',1;
	END
GO

Rate

1 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

1 (1)

You rated this post out of 5. Change rating