Remove a primary database from an Always On availability group

Applies to: SQL Server

This topic describes how to remove both the primary database and the corresponding secondary database(s) from an Always On availability group by using SQL Server Management Studio, Transact-SQL, or PowerShell in SQL Server.

Prerequisites and Restrictions

• This task is supported only on primary replicas. You must be connected to the server instance that hosts the primary replica.

Permissions

Requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.

Using SQL Server Management Studio

To remove an availability database

1. In Object Explorer, connect to the server instance that hosts the primary replica of the database or databases to be removed, and expand the server tree.

2. Expand the Always On High Availability node and the Availability Groups node.

3. Select the availability group, and expand the Availability Databases node.

4. This step depends on whether you want to remove multiple databases groups or only one database, as follows:

   • To remove multiple databases, use the Object Explorer Details pane to view and select all the databases that you want to remove. For more information, see Use the Object Explorer Details to Monitor Availability Groups (SQL Server Management Studio).

   • To remove a single database, select it in either the Object Explorer pane or the Object Explorer Details pane.

5. Right-click the selected database or databases, and select Remove Database from Availability Group in the command menu.

6. In the Remove Databases from Availability Group dialog box, to remove all the listed databases, click OK. If you do not want to remove all them, click Cancel.

Using Transact-SQL

To remove an availability database

1. Connect to the server instance that hosts the primary replica.

2. Use the ALTER AVAILABILITY GROUP statement, as follows:

   ALTER AVAILABILITY GROUP group_name REMOVE DATABASE availability_database_name

   where group_name is the name of the availability group and database_name is the name of the database to be removed.

   The following example removes a databases named Db6 from the MyAG availability group.

   ALTER AVAILABILITY GROUP MyAG REMOVE DATABASE Db6;  
   

Using PowerShell

To remove an availability database

1. Change directory (cd) to the server instance that hosts the primary replica.

2. Use the Remove-SqlAvailabilityDatabase cmdlet, specifying the name of the availability database to be removed from the availability group. When you are connected to the server instance that hosts the primary replica, the primary database and its corresponding secondary databases are all removed from the availability group.

   For example, the following command removes the availability database MyDb9 from the availability group named MyAg. Because the command is executed on the server instance that hosts the primary replica, the primary database and all its corresponding secondary databases are removed from the availability group. Data synchronization will no longer occur for this database on any secondary replica.

   Remove-SqlAvailabilityDatabase `   
   -Path SQLSERVER:\Sql\PrimaryComputer\InstanceName\AvailabilityGroups\MyAg\AvailabilityDatabases\MyDb9
   

   Note

   To view the syntax of a cmdlet, use the Get-Help cmdlet in the SQL Server PowerShell environment. For more information, see Get Help SQL Server PowerShell.

To set up and use the SQL Server PowerShell provider

• SQL Server PowerShell Provider

Follow Up: After Removing an Availability Database from an Availability Group

Removing an availability database from its availability group ends data synchronization between the former primary database and the corresponding secondary databases. The former primary database remains online. Every corresponding secondary database is placed in the RESTORING state.

At this point there are alternative ways of dealing with a removed secondary database:

• If you no longer need a given secondary database, you can drop it.

  For more information, see Delete a Database.

• If you want to access a removed secondary database after it has been removed from the availability group, you can recover the database. However, if you recover a removed secondary database, two divergent, independent databases that have the same name are online. You must make sure that clients can access only one of them, typically the most recent primary database.

  For more information, see Recover a Database Without Restoring Data (Transact-SQL).

See Also

Overview of Always On Availability Groups (SQL Server)
Remove a Secondary Database from an Availability Group (SQL Server)