Skip to content

Latest commit

 

History

History
34 lines (26 loc) · 1.22 KB

ss-availability-group-rs-postactivity.md

File metadata and controls

34 lines (26 loc) · 1.22 KB
author ms.author ms.date ms.service ms.topic
rwestMSFT
randolphwest
01/29/2024
sql
include

Add a database to the availability group

Ensure that the database you add to the availability group is in the full recovery model and has a valid log backup. If the database is a test database or a newly created database, take a database backup. To create and back up a database called db1, run the following Transact-SQL script on the primary SQL Server instance:

CREATE DATABASE [db1];
ALTER DATABASE [db1] SET RECOVERY FULL;
BACKUP DATABASE [db1]
   TO DISK = N'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\db1.bak';

To add a database called db1 to an availability group called ag1, run the following Transact-SQL script on the primary SQL Server replica:

ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [db1];

Verify that the database is created on the secondary servers

To see whether the db1 database was created and is synchronized, run the following query on each secondary SQL Server replica:

SELECT * FROM sys.databases WHERE name = 'db1';
GO
SELECT DB_NAME(database_id) AS 'database', synchronization_state_desc FROM sys.dm_hadr_database_replica_states;