One of the reasons I love working with SQL Server is the strength of the community. The willingness of experts in the field to share their knowledge is amazing. My favorite current example of this is the DBATools project. DBATools is a suite of PowerShell functions for automating and maintaining SQL Server environments. DBATools can be downloaded from this link.
This is the first in a series of blog posts, I'll demonstrate ways to leverage DBATools functions to simplify management of your SQL Server environments.
Always On Availability Groups (AGs) were introduced in SQL Server 2012 to provide high availability and disaster recovery for groups of databases. One of the challenges running AGs is a database participating in an AG cannot be restored. Instead, the database must be removed from the AG, restored, and put back into the AG. The PowerShell script below automates the AG restore process by performing the following steps:
1) Confirm backup file provided exists
2) Confirm the replica provided as primary is the primary replica for the AG
3) Confirm that the file share provided exists and is accessible by SQL Server service
4) Remove the database from the AG and drop it from the secondary replicas
5) Restore the database to the primary replica using the backup file provided
6) Add the database back to the AG using DBATools function Add-DbaAgDatabase
7) For SQL Server versions 2012 and 2014, Add-DbaAgDatabase uses backup and restore to add the database back to the availability group
8) For SQL Server version 2016 and newer, Add-DbaAgDatabase uses automatic seeding to add the database back to the availability group
The PowerShell code can be downloaded from the link below. You will have to rename the file with a .ps1 extension after download.
RestoreAgDatabaseFunction.txt