July 4, 2013 at 8:46 am
Hi all,
I'm really lost as to why i can't get this to work.
I have an AlwaysOn AG with 42 databases in the group. The option to suspend data movement is to issue the suspend statement one at a time like this:
ALTER DATABASE [DBName] SET HADR SUSPEND;
I like to build a script that can suspend them all at once. My code looks like this:
Declare @database_name VARCHAR(100)
Declare @suspendname VARCHAR(100)
DECLARE db_cursor CURSOR FOR
select database_name from sys.availability_databases_cluster
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @database_name
WHILE @@FETCH_STATUS = 0
BEGIN
Set @suspendname = 'ALTER DATABASE ['+@database_name+'] SET HADR SUSPEND;'
execute @suspendname
--PRINT @suspendname
FETCH NEXT FROM db_cursor INTO @database_name
END
CLOSE db_cursor
DEALLOCATE db_cursor
The error i get is this:
Msg 203, Level 16, State 2, Line 19
The name 'ALTER DATABASE [Div_Test] SET HADR SUSPEND;' is not a valid identifier.
July 4, 2013 at 8:49 am
Try this:
...
execute (@suspendname)
...
John
July 4, 2013 at 8:56 am
WUPTI
Great - that worked - got another error, but I know how to fix that.
Thnx a million.
Viewing 3 posts - 1 through 3 (of 3 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