MS SQL Always On High Availability Group бэкап БД на основной реплике

Для выполнения бэкапа БД на основной реплике в составе Always On High Availability Group на каждом сервер создать план обслуживания (Maintance Plan), например, BackupAOHAG. Для каждой БД добавить T-SQL блок в который добавить скрипт:

DECLARE @DBNAME VARCHAR(50)
	— Указать имя БД!!!
	SET @DBNAME = ‘DATABASE_NAME_FOR_BACKUP’
DECLARE @BACKUPPATH VARCHAR(100)
	— Указать сетевой каталог назначения бэкапа!!!
	SET @BACKUPPATH = ‘\\srv-fs01\backup_dbms\’
DECLARE @preferredReplica int
	SET @preferredReplica  = (SELECT sys.fn_hadr_backup_is_preferred_replica (@DBNAME))
DECLARE @DATEFILE  VARCHAR(50)
DECLARE @d DATETIME = GETDATE();
	SET @DATEFILE = (SELECT FORMAT( @d, ‘yyyy_MM_dd’, ‘en-US’ ) AS ‘DateTime Result’)
DECLARE @DeleteDate7 datetime
	— Указать количество дней после которого бэкапы удаляются!!!
	SET @DeleteDate7 = DateAdd(day, -7, GetDate())
DECLARE @DBFULLPATH_BAK VARCHAR(100)
	SET @DBFULLPATH_BAK = (@BACKUPPATH + @DBNAME + ‘_’ + @DATEFILE + ‘.bak’)
DECLARE @DBFULLPATH_TRN VARCHAR(100)
	SET @DBFULLPATH_TRN = (@BACKUPPATH + @DBNAME + ‘_’ + @DATEFILE + ‘.trn’)
DECLARE @BACKUPNAME  VARCHAR(50)
	SET @BACKUPNAME = (@DBNAME + ‘_’ + @DATEFILE)
IF (@preferredReplica = 1)
	BEGIN
		SELECT sys.fn_hadr_backup_is_preferred_replica ( @DBNAME) As PrefferedReplicaState;
		–Create Backup DB
		exec ( ‘USE ‘ + @DBNAME + ‘ BACKUP DATABASE [‘ + @DBNAME + ‘] TO  DISK = N”’ + @DBFULLPATH_BAK + ”” + ‘WITH  RETAINDAYS = 2, NOFORMAT, NOINIT,  NAME = N”’+ @BACKUPNAME + ”’, SKIP, REWIND, NOUNLOAD,  STATS = 10′ )
		–Create Backup TLOG
		exec ( ‘USE ‘ + @DBNAME + ‘ BACKUP LOG      [‘ + @DBNAME + ‘] TO  DISK = N”’ + @DBFULLPATH_TRN + ”” + ‘WITH  RETAINDAYS = 2, NOFORMAT, NOINIT,  NAME = N”’+ @BACKUPNAME + ”’, SKIP, REWIND, NOUNLOAD,  STATS = 10′ )
		–Shrink DB
		exec ( ‘USE ‘ + @DBNAME + ‘ DBCC SHRINKDATABASE(N”’ + @DBNAME + ”’, 10, TRUNCATEONLY)’ )
		— CleanUP
		exec (‘EXECUTE master.dbo.xp_delete_file 0,N”’ + @BACKUPPATH + ””+’,N”’+’bak”,N”’ + @DeleteDate7 +”” + ‘,1’)
		exec (‘EXECUTE master.dbo.xp_delete_file 0,N”’ + @BACKUPPATH + ””+’,N”’+’trn”,N”’ + @DeleteDate7 +”” + ‘,1’)
	END

DECLARE @DBNAME VARCHAR(50)

— Указать имя БД!!!

SET @DBNAME = ‘DATABASE_NAME_FOR_BACKUP’

DECLARE @BACKUPPATH VARCHAR(100)

— Указать сетевой каталог назначения бэкапа!!!

SET @BACKUPPATH = ‘\\srv-fs01\backup_dbms\’

DECLARE @preferredReplica int

SET @preferredReplica = (SELECT sys.fn_hadr_backup_is_preferred_replica (@DBNAME))

DECLARE @DATEFILE VARCHAR(50)

DECLARE @d DATETIME = GETDATE();

SET @DATEFILE = (SELECT FORMAT( @d, ‘yyyy_MM_dd’, ‘en-US’ ) AS ‘DateTime Result’)

DECLARE @DeleteDate7 datetime

— Указать количество дней после которого бэкапы удаляются!!!

SET @DeleteDate7 = DateAdd(day, –7, GetDate())

DECLARE @DBFULLPATH_BAK VARCHAR(100)

SET @DBFULLPATH_BAK = (@BACKUPPATH + @DBNAME + ‘_’ + @DATEFILE + ‘.bak’)

DECLARE @DBFULLPATH_TRN VARCHAR(100)

SET @DBFULLPATH_TRN = (@BACKUPPATH + @DBNAME + ‘_’ + @DATEFILE + ‘.trn’)

DECLARE @BACKUPNAME VARCHAR(50)

SET @BACKUPNAME = (@DBNAME + ‘_’ + @DATEFILE)

IF (@preferredReplica = 1)

BEGIN

SELECT sys.fn_hadr_backup_is_preferred_replica ( @DBNAME) As PrefferedReplicaState;

–Create Backup DB

exec ( ‘USE ‘ + @DBNAME + ‘ BACKUP DATABASE [‘ + @DBNAME + ‘] TO DISK = N’” + @DBFULLPATH_BAK + ”” + ‘WITH RETAINDAYS = 2, NOFORMAT, NOINIT, NAME = N’”+ @BACKUPNAME + ”‘, SKIP, REWIND, NOUNLOAD, STATS = 10’ )

–Create Backup TLOG

exec ( ‘USE ‘ + @DBNAME + ‘ BACKUP LOG [‘ + @DBNAME + ‘] TO DISK = N’” + @DBFULLPATH_TRN + ”” + ‘WITH RETAINDAYS = 2, NOFORMAT, NOINIT, NAME = N’”+ @BACKUPNAME + ”‘, SKIP, REWIND, NOUNLOAD, STATS = 10’ )

–Shrink DB

exec ( ‘USE ‘ + @DBNAME + ‘ DBCC SHRINKDATABASE(N’” + @DBNAME + ”‘, 10, TRUNCATEONLY)’ )

— CleanUP

exec (‘EXECUTE master.dbo.xp_delete_file 0,N’” + @BACKUPPATH + ””+‘,N’”+‘bak’‘,N’” + @DeleteDate7 +”” + ‘,1’)

exec (‘EXECUTE master.dbo.xp_delete_file 0,N’” + @BACKUPPATH + ””+‘,N’”+‘trn’‘,N’” + @DeleteDate7 +”” + ‘,1’)

END