Query to remove all databases from AG

Question

Post reply

Query to remove all databases from AG

ramyours2003

SSChampion

Points: 12266
More actions
July 19, 2022 at 6:24 am

#4065117

As part of DR we need to remove all the databases from different AG's , each AG is consists of 20+ databases , i need a query to generate script for removing all databases with respect to specific AG group , .

Thanks,.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113445 More actions · Answer 1

So you want to query the DMVs sys.availability_groups, sys.dm_hadr_database_replica_states and sys.databases.

These 3 will get you the databases per AG.

From there you can then write a loop or whatever based approach you prefer to then dynamically build the ALTER DATABASE SET HADR OFF command to remove that DB from the replica.

ramyours2003 SSChampion Points: 12266 More actions · Answer 2

Thanks for the suggestion and reply , please provide the query as i am not proficient in programming

Ant-Green SSC Guru Points: 113445 More actions · Answer 3

Please have a go and try, the more you try the better you will become.

Post the query you use and it will show initiative that you have at least tried.

It will help you both personally and professionally that you try without being handed the answer.

First start by getting the list of databases for the AG then build out into the removal stage.

Ant-Green SSC Guru Points: 113445 More actions · Answer 4

Or if you don’t want to do it yourself, look at DBATools and Remove-DbaAgDatabase which will do it all for you.