Для выполнения бэкапа БД на основной реплике в составе 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 |
Leave a Reply