bschelst/ February 17, 2020/ SQL-Server/ 1 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

1 Comment

  1. I’m truly enjoying the design and layout of your website.
    It’s a very easy on the eyes which makes it much more pleasant for me to come here and visit more often. Did you hire out a developer to create your theme?
    Excellent work!

    Feel free to surf to my web-site: new

Leave a Comment

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>
*
*