Table: azure_sql_server_database_long_term_retention_policies

This table shows data for Azure SQL Server Database Long Term Retention Policies.

https://learn.microsoft.com/en-us/rest/api/sql/2021-11-01/long-term-retention-policies/list-by-database?tabs=HTTP#longtermretentionpolicy (opens in a new tab)

The primary key for this table is id.

Relations

This table depends on azure_sql_server_databases.

Columns

NameType
_cq_iduuid
_cq_parent_iduuid
subscription_idutf8
propertiesjson
id (PK)utf8
nameutf8
typeutf8

Example Queries

These SQL queries are sampled from CloudQuery policies and are compatible with PostgreSQL.

Long-term geo-redundant backup should be enabled for Azure SQL Databases

SELECT
  'Long-term geo-redundant backup should be enabled for Azure SQL Databases'
    AS title,
  s.subscription_id,
  rp.id,
  CASE
  WHEN rp.id IS NULL
  OR (
      rp.properties->>'weeklyRetention' IS NOT DISTINCT FROM 'PT0S'
      AND rp.properties->>'monthlyRetention' IS NOT DISTINCT FROM 'PT0S'
      AND rp.properties->>'yearlyRetention' IS NOT DISTINCT FROM 'PT0S'
    )
  THEN 'fail'
  ELSE 'pass'
  END
FROM
  azure_sql_servers AS s
  LEFT JOIN azure_sql_server_databases AS asd ON s._cq_id = asd._cq_parent_id
  LEFT JOIN azure_sql_server_database_long_term_retention_policies AS rp ON
      asd._cq_id = rp._cq_parent_id;