Drop and Recreate database from Always On High Availability From Script

If you need to re-create the database, you have to remove the database from the Always On High Availability Group first before you can drop the database.

To recreate the Always On Availability, create a backup of the database on the primary server and then restore the backup to the replica server.

Drop Database from Always On High Availability

-- Primary Server
-- 1.  Drop database from Availability Group.
USE [master]
GO
ALTER AVAILABILITY GROUP [MyAG] REMOVE DATABASE [MyDB];

Re-add Database back to Always On High Availability

-- Primary Server
-- 2.  Backup database file and transaction log.
BACKUP DATABASE [MyDB] 
TO  DISK = N'\\server01\AG_backup\MyDB.bak' WITH NOFORMAT, NOINIT,  
NAME = N'Full Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
 
BACKUP LOG [MyDB] TO  DISK = N'\\server01\AG_backup\\MyDB.trn' WITH NOFORMAT, NOINIT,  
NAME = N'Log Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

-- 3.  Add database back to Availability Group.
ALTER AVAILABILITY GROUP [MyAG] ADD DATABASE [MyDB];

-----------------

--  Replica Server
USE [master]
GO

-- 4.  Drop existing database on replica server.
DROP DATABASE MyDB

-- 5.  Re-create database from backup.
RESTORE DATABASE [MyDB] FROM  DISK = N'\\server01\AG_backup\MyDB.bak' 
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
GO
 
RESTORE LOG [MyDB] FROM  DISK = N'\\server01\AG_backup\MyDB.trn' WITH  FILE = 1,  
NORECOVERY,  NOUNLOAD,  STATS = 10
GO

-- 6.  Re-join database to Availability Group.
ALTER DATABASE [MyDB] SET HADR AVAILABILITY GROUP = [MyAG];

Note:

Make sure the Always On database is completely removed from the replica server first before adding the database to Always On on the primary server. Also make sure there are no existing log backup jobs running or you will get the error “remote copy of database is not related to the local copy of the database” message.

Make sure service account running SQL Server has read/write access to the shared folder\\server01\AG_backup

Log Backup Note:

If you’re taking log backups, you’ll have to stop them while you’re setting up a new secondary, otherwise the log will truncate on the primary, causing this error.

So, disable log backups, add the database to the Availability Group on the primary, and either wait for automatic seeding to complete its tasks, or manually restore (with norecovery) a copy of the database on the secondary, then join it to the AG. Once the secondary copies of the database are successfully synchronized with the primary, resume the transaction log backups.