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