SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

A framework to backup databases on Availability Groups

By Gaby Abed,

This script generates a health status of each db in an AG (and list of DBs not in AG) and uses an IF/THEN block to check if the synchronization_state_desc is healthy. Feel free to insert in the part marked INSERT BACKUP CODE HERE your preferred backup scripts, or trusted third party ones such as Ola Hallengren's.  Please note, this is very rough, and does not check for other criteria such as the DB being online, and what kind of backup (Full, Diff, Tlog) is required, but hopefully this will be usefuI.
Ideally, this script and modifications can be wrapped in a proc that is deployed to all servers, both standalone and those in HA's, as a job.  In theory, if you want, you can backup the transaction logs on the secondary, and full on primary.  This would require a bit of a tweak with the IF/THEN block.

Total article views: 149 | Views in the last 30 days: 149
 
Tags
alwayson    
availability group (ag)    
backup    
ha    
high availability    
 
Contribute