Backup user and system databases outside HA availability Group

• lianvh 89542

  SSCommitted

  Points: 1561

  More actions

  September 2, 2016 at 3:41 am

  #313097

  Good day,

  we need to backup databases (user and system) not forming part of the High Availability Group and this needs to be dynamic . Also when the database is forming part of the primary availability group should be backed up. I looked into the sys.databases as well as the availability groups dmvs, but had no success . Any ideas ?

  Thanks.

  Lian

• Nicholas Cain

  SSC-Dedicated

  Points: 33009

  More actions

  September 2, 2016 at 8:43 am

  #1898994

  This will give you a list of local databases that are not in an AG, or where the local server is the AG primary...

  select name from sys.databases where database_id not in (select database_id from sys.dm_hadr_database_replica_states)

  union all

  select name from sys.databases where database_id in (select database_id from sys.dm_hadr_availability_replica_states where is_local = 1)

  ---

  
  Shamless self promotion - read my blog http://sirsql.net