Always On - Manual Adding Database to Availability Group

Adding database to Availability Group in AlwaysON is done from SSMS:

• Connect to the Primary SQL Server Instance, go to "Always On Availability Group", open "Availability Groups".
• Right click the Availability Group and choose "Add Database".
• Follow the wizard to add database to the Availability Group.

In case the wizard fails to complete the tasks, you can add the database manually:

The steps are based on the following link:

• https://www.mssqltips.com/sqlservertip/5437/adding-a-database-to-an-existing-sql-server-always-on-configuration/

• On Primary server, Create a full backup, which can be used to create the secondary database

BACKUP DATABASE [database_name] TO DISK = 'G:\database_name.bak' WITH INIT
GO

BACKUP LOG [database_name] TO DISK = 'G:\database_name.trn' WITH INIT
GO

• Copy the full and log backup files to the secondary server that hosts the replica.

• On Secondary server, restore the full backup and log backup

RESTORE DATABASE [database_name] FROM DISK = 'G:\mscrm_ha\database_name.bak' WITH NORECOVERY
GO

RESTORE DATABASE [database_name] FROM DISK = 'G:\mscrm_ha\database_name.trn' WITH NORECOVERY
GO

• On Primary server, add this newly created database to the Always On Availability Group

ALTER AVAILABILITY GROUP AVAILABILITY_GROUP_NAME ADD DATABASE database_name;  
GO

• On Secondary server, add this newly created database to the Always On Availability Group

ALTER DATABASE database_name SET HADR AVAILABILITY GROUP = AVAILABILITY_GROUP_NAME;
GO