February 12, 2018 at 11:28 am
Hello Gentlemen,
I am trying to fing a way to resume data movement on all database in a AG after a forced failover instead of going one by one or using ALTER DATABASE [YourDatabase] SET HADR RESUME, Do any of you have a good idea or script that i could uses.
Cheers!
February 12, 2018 at 8:07 pm
Hi,
That is only option in this case. You can use msforeachdb in order to make it simple. Or you can write your own script.
February 14, 2018 at 7:00 am
Hello I tried this but this does not work... if i remove my condition from the foreach no problem i dont know what iam doing wrong here as with the code below it completes succesfully but does not resume data movement!!
EXEC sp_msforeachdb
'IF ''?'' IN (select distinct database_name from master.sys.dm_hadr_database_replica_cluster_states)
BEGIN
USE master; ALTER DATABASE [?] SET HADR RESUME
END'
February 14, 2018 at 7:58 am
I use this, works all the time
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
February 14, 2018 at 8:48 am
This works!! greatly appreciated thank you very much!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy