Remove a Secondary Database from an Availability Group (SQL Server)

Applies to: SQL Server

This topic describes how to remove a secondary database 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 secondary replicas. You must be connected to the server instance that hosts the secondary replica from which the database is to be removed.

Permissions

Requires ALTER permission on the database.

Using SQL Server Management Studio

To remove a secondary database from an availability group

1. In Object Explorer, connect to the server instance that hosts the secondary replica from which you want to remove one or more secondary databases, 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 Secondary Database in the command menu.

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

Using Transact-SQL

To remove a secondary database from an availability group

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

2. Use the SET HADR clause of the ALTER DATABASE statement, as follows:

   ALTER DATABASE database_name SET HADR OFF

   where database_name is the name of a secondary database to be removed from the availability group to which it belongs.

   The following example removes the local secondary database MyDb2 from its availability group.

   ALTER DATABASE MyDb2 SET HADR OFF;  
   GO  
   

Using PowerShell

To remove a secondary database from an availability group

1. Change directory (cd) to the server instance that hosts the secondary 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 a server instance that hosts a secondary replica, only the local secondary database is removed from the availability group.

   For example, the following command removes the secondary database MyDb8 from the secondary replica hosted by the server instance named SecondaryComputer\Instance. Data synchronization to the removed secondary databases ceases. This command does not affect the primary database or any other secondary databases.

   Remove-SqlAvailabilityDatabase `  
   -Path SQLSERVER:\Sql\SecondaryComputer\InstanceName\AvailabilityGroups\MyAg\AvailabilityDatabases\MyDb8  
   

   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 a Secondary Database from an Availability Group

When a secondary database is removed, it is no longer joined to the availability group and all information about the removed secondary database is discarded by the availability group. The removed secondary database is placed in the RESTORING state.

Tip

For a short time after removing a secondary database, you might be able to restart Always On data synchronization on the database by re-joining it to the availability group. For more information, see Join a Secondary Database to an Availability Group (SQL Server).

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

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

  For more information, see DROP DATABASE (Transact-SQL) or 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 the current 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 Primary Database from an Availability Group (SQL Server)