T-SQL: Resume or suspend all always on databases

You can use the t-sql code to resume/suspend all databases from the always on group.
The code below will resume SQL data movement for all databases:

declare @cmd varchar(max)
declare @cmd_suspend varchar(max)
declare @cmd_resume varchar(max)
declare c1 cursor read_only for
select 'ALTER DATABASE ['+DB_name(database_id)+'] SET HADR ' from master.sys.dm_hadr_database_replica_states where is_local = 1

open c1
fetch next from c1 into @cmd

while @@FETCH_STATUS = 0
begin
set @cmd_resume = @cmd + 'RESUME'
print @cmd_resume
exec(@cmd_resume)
print '--executed --> ' + @cmd_resume
fetch next from c1 into @cmd
end

close c1
deallocate c1

The code below will suspend SQL data movement for all databases:

declare @cmd varchar(max)
declare @cmd_suspend varchar(max)
declare @cmd_resume varchar(max)
declare c1 cursor read_only for
select 'ALTER DATABASE ['+DB_name(database_id)+'] SET HADR ' from master.sys.dm_hadr_database_replica_states where is_local = 1

open c1
fetch next from c1 into @cmd

while @@FETCH_STATUS = 0
begin
set @cmd_suspend = @cmd + 'SUSPEND'
print @cmd_suspend
exec(@cmd_suspend)
print '--executed --> ' + @cmd_suspend
--set @cmd_resume = @cmd + 'RESUME'
--print @cmd_resume
--exec(@cmd_resume)
--print '--executed --> ' + @cmd_resume
fetch next from c1 into @cmd
end

close c1
deallocate c1
Share your love