Table: gcp_sql_backup - Query Google Cloud SQL Backups using SQL

Google Cloud SQL Backups is a service within Google Cloud that allows you to create, configure, and manage backups for your SQL databases. It provides a streamlined way to safeguard your data and maintain business continuity. Google Cloud SQL Backups helps you ensure data integrity, recover from disasters, and meet compliance requirements.

Table Usage Guide

The gcp_sql_backup table provides insights into backups within Google Cloud SQL. As a database administrator, explore backup-specific details through this table, including backup configurations, timings, and statuses. Utilize it to uncover information about backups, such as their configurations, the timing of the last successful backup, and the status of ongoing backups.

Examples

Basic info

Determine the status and timing details of your Google Cloud Platform SQL backups. This can help you understand backup health and scheduling, essential for maintaining data integrity and planning recovery scenarios.

select
  id,
  instance_name,
  description,
  status,
  end_time,
  enqueued_time,
  start_time,
  window_start_time
from
  gcp_sql_backup;

select
  id,
  instance_name,
  description,
  status,
  end_time,
  enqueued_time,
  start_time,
  window_start_time
from
  gcp_sql_backup;

Count of backups by their type (i.e AUTOMATED and ON_DEMAND)

Determine the distribution of backup types to understand your database's backup strategy. This can help in assessing the balance between automated and on-demand backups, and optimize your data protection approach.

select
  type,
  count(*) as backup_count
from
  gcp_sql_backup
group by
  type;

select
  type,
  count(*) as backup_count
from
  gcp_sql_backup
group by
  type;

Get the error message if the backup failed

Determine the areas in which a backup failure has occurred in your Google Cloud Platform SQL database. This query can be used to identify the specific instances and error messages associated with each failure, helping you troubleshoot and resolve issues more efficiently.

select
  id,
  instance_name,
  e ->> 'code' as error_code,
  e ->> 'message' as error_message
from
  gcp_sql_backup,
  jsonb_array_elements(error) as e
where
  status = 'FAILED';

select
  b.id,
  b.instance_name,
  json_extract(e.value, '$.code') as error_code,
  json_extract(e.value, '$.message') as error_message
from
  gcp_sql_backup as b,
  json_each(error) as e
where
  status = 'FAILED';

Query examples

Schema for gcp_sql_backup

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
akas	jsonb		Array of globally unique identifier strings (also known as) for the resource.
description	text		A user-specified, human-readable description of the backup run.
disk_encryption_configuration	jsonb		Specifies the encryption configuration for the disk.
disk_encryption_status	jsonb		Specifies the encryption status of the disk.
end_time	timestamp with time zone		Specifies the time when the backup operation completed.
enqueued_time	timestamp with time zone		Specifies the time when the run was enqueued.
error	jsonb		Information about why the backup operation failed. This is only present if the run has the FAILED status.
id	bigint	=	An unique identifier for the backup run.
instance_name	text	=	The name of the Cloud SQL instance.
kind	text		The type of the resource.
location	text		The GCP multi-region, region, or zone in which the resource is located.
project	text		The GCP Project in which the resource is located.
self_link	text		The server-defined URL for the resource.
start_time	timestamp with time zone		Specifies the time when the backup operation actually started.
status	text		Specifies the status of the backup run.
title	bigint		Title of the resource.
type	text		Specifies the type of the backup run. Value can be either 'AUTOMATED' or 'ON_DEMAND'.
window_start_time	timestamp with time zone		Specifies the start time of the backup window during which this the backup was attempted.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- gcp

You can pass the configuration to the command with the --config argument:

steampipe_export_gcp --config '<your_config>' gcp_sql_backup