use hqms
CREATE PROCEDURE [dbo].[BACK]
AS
-- =============================================
--1. 优先在辅助副本上执行备份。
--2. 多个辅助副本在线时,在备份优先级高的辅助副本上执行。
--3. 辅助副本优先级相同时,在主机名较小的辅助副本上执行。
--4. 只有所有辅助副本都故障时才在主副本执行备份。
--SELECT
--CASE ags.primary_replica WHEN ar.replica_server_name THEN '主副本' ELSE '辅助副本' END 副本类型,
--CASE WHEN backup_priority > 0 THEN '可备份' ELSE '不可用' END 是否可备份,
--ar.replica_server_name 副本主机名,ar.backup_priority 备份优先级
--FROM sys.availability_replicas ar INNER JOIN sys.dm_hadr_availability_group_states ags
--ON ags.group_id = ar.group_id
--inner join sys.dm_hadr_cluster_members cm on ar.replica_server_name=cm.member_name and cm.member_state=1
--ORDER BY CASE ags.primary_replica WHEN ar.replica_server_name THEN '1' ELSE '0' END,
--ar.backup_priority ASC
-- =============================================
BEGIN
--需要备份的数据库名
declare @dbname nvarchar(250)='hqms'
--备份路径
declare @path nvarchar(250)='D:\backup\'
--查询主机名
DECLARE @当前登录主机 VARCHAR(100) = (select @@SERVERNAME)
--查询执行备份服务的主机名:选取辅助副本中备份优先级较高的节点
DECLARE @执行备份服务的主机 VARCHAR(100) = (
SELECT top 1 ar.replica_server_name 副本主机名
FROM sys.availability_replicas ar
INNER JOIN sys.dm_hadr_availability_group_states ags ON ags.group_id = ar.group_id
inner join sys.dm_hadr_cluster_members cm on ar.replica_server_name = cm.member_name and cm.member_state=1
where ar.backup_priority > 0
ORDER BY CASE ags.primary_replica WHEN ar.replica_server_name THEN '1' ELSE '0' END ASC,ar.backup_priority ,ar.replica_server_name)
IF ( @当前登录主机 = @执行备份服务的主机 )
BEGIN
declare @filename nvarchar(250)
set @filename=replace(replace(replace(REPLACE(convert(varchar(50),getdate(),121),'-','_'),' ','_'),':',''),'.','_')
set @filename=@path + @执行备份服务的主机 + '_' + @dbname + '_Backup_' + @filename + '.bak'
BACKUP DATABASE @dbname TO DISK =@filename WITH COMPRESSION,copy_only,noformat,noinit,skip,rewind,nounload, stats = 10
END
END