T-SQL: Resume or suspend all always on databases

Bschelst/ February 17, 2020/ SQL-Server/ 0 comments

You can use the t-sql code to resume/suspend all databases from the always on group.

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 this Post

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*