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