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

Last modified: June 23, 2021

Author

Comments

Write a Reply or Comment