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