Daily, weekly restores of a SQL database to SQL AlwaysON

Question

Our PT environment is SQL AlwaysOn just like our Prod environment. App Team now wants to drop a PROD BAK weekly, and maybe some Daily prior to a release; then restore to the PT AO AG.

Not wanting to use a Linked Server, what other ways could I drop the secondary DB

1. Check if Primary node
2. Remove DB from AG (puts secondary DB in restoring and which needs dropped)
3. Restore DB to Primary
4. Set to full (stand alone)
5. Auto-seeding will sync to other node

Answer 1

use dbatools - its free, opensource and highly customizable.

What I would recommend depending downtime and disk space you have available (I do it daily for 15TB daily using dbatools)

• Check if Primary node - https://docs.dbatools.io/Get-DbaAgDatabase
• Remove DB from AG (puts secondary DB in restoring and which needs dropped) https://docs.dbatools.io/Remove-DbaAgDatabase
• Restore DB to Primary https://docs.dbatools.io/Add-DbaAgDatabase
  • Set to full (stand alone)
  • Auto-seeding will sync to other node

Reference : Refresh SQL Server Always On Availability Group databases using DBATools PowerShell

• user3191894: Thanks, exactly what was used!