Operating databases

Querying each database

Almost every database has an editor built into the console, so you don't need a third-party client to run a query.

gcloud sql connect my-instance --user=root

gcloud spanner databases execute-sql example-database \
  --instance=example-instance --sql='SELECT * FROM MyTable WHERE MyKey = 1'

bq query --use_legacy_sql=false 'SELECT COUNT(*) FROM `my-project.sales.orders`'

gcloud components install cbt
cbt listinstances

The cbt CLI doesn't implement smart retries or error handling; for production Google recommends a Bigtable client library or the console. Its options (project, instance…) can be stored in a .cbtrc file.

A dry run validates the query without running it and returns an estimate of the bytes it would process (the on-demand cost). It uses no slots and is not charged. You can take the estimate to the pricing calculator.

bq query --use_legacy_sql=false --dry_run \
  'SELECT name FROM `my-project.sales.customers` WHERE country = "ES"'

Cloud SQL backups are incremental and encrypted with Google keys or CMEK. When you create a new instance, you choose one of two backup options:

gcloud sql backups create --async --instance=my-instance
gcloud sql backups list --instance=my-instance
gcloud sql backups restore BACKUP_ID --restore-instance=target-instance

• Restoring onto an existing instance overwrites all of its data, including previous point-in-time recovery logs.
• When you delete an instance, its on-demand and automated backups become retained backups at the project level, which you can restore to a new or existing instance.
• Restoring from a backup doesn't keep the IP address of the deleted instance. To recover it with its IP, Cloud SQL keeps backups of deleted instances for 4 days, and within that window you can ask Customer Care to recover it exactly as it was.

Point-in-time recovery (PITR)

PITR restores a primary instance to its state at a specific moment, whether it is live or deleted. It always creates a new instance: you can't perform PITR onto an existing instance. In MySQL it requires binary logging and backups enabled, with continuous logs since the last backup before the incident. The recommended approach is a timestamp; in the console you use Create clone and Clone from an earlier point in time.

gcloud sql instances clone SOURCE_INSTANCE NEW_INSTANCE \
  --point-in-time='2026-10-24T08:15:00.000Z'

Events at or after that timestamp aren't reflected in the new instance.

Backups in Spanner, Firestore, AlloyDB, and Bigtable

gcloud spanner backups create my-backup --instance=my-instance \
  --database=my-db --retention-period=2w

gcloud firestore backups schedules create --database='(default)' \
  --recurrence=daily --retention=4w

gcloud firestore databases restore \
  --source-backup=projects/PROJECT_ID/locations/LOCATION/backups/BACKUP_ID \
  --destination-database=restored-db

gcloud bigtable backups create my-backup --instance=my-instance \
  --cluster=my-cluster --table=my-table --retention-period=7d

All Google Cloud data is encrypted at rest with AES-256 and Google keys, with nothing to configure. With CMEK you create the keys yourself in Cloud KMS: you control their rotation, access, and state, you see their usage logs, and you meet regulations that require your own keys. The resource's service agent handles encryption and decryption transparently.

• Cloud KMS Autokey creates key rings and keys when the resource is created and grants the roles to the service agents. Google recommends it if its keys meet your requirements.
• With Cloud SQL enhanced backups and CMEK, the Backup and DR service agent needs roles/cloudkms.cryptoKeyEncrypterDecrypter on the key.
• Cloud KMS charges per key version and per cryptographic operation; default encryption is free.

Every load, export, query, or copy creates an asynchronous job. Its states are PENDING, RUNNING, and DONE; a finished job that failed includes errorResult. The cost is billed to the project that runs the job.

bq ls --jobs --filter="states:RUNNING,PENDING"
bq show --jobs JOB_ID
bq cancel JOB_ID
bq wait JOB_ID

The console's monitoring interface shows the jobs list (running jobs and those from the last 30 days), the job graph with each step, execution details, metrics, estimated cost, recommendations, autoscaling charts, turnkey alerts for streaming, pipeline logs, and data sampling.

gcloud dataflow jobs list --region=europe-west1
gcloud dataflow jobs describe JOB_ID --region=europe-west1
gcloud dataflow jobs drain JOB_ID --region=europe-west1
gcloud dataflow jobs cancel JOB_ID --region=europe-west1

Dataflow jobs can't be deleted, only archived; completed jobs are removed after 30 days. Before stopping a streaming pipeline, consider a snapshot to keep its state.

Database Center

An AI-assisted dashboard that gives you one centralized view of your whole database fleet, across projects. It uses data from your projects and Security Command Center to show health issues and recommendations, and it has a Gemini chat to ask about them.

• It shows the inventory, the engines and versions in use, which critical databases aren't optimized for availability, which have no backups, which don't follow security best practices, and which aren't compliant with industry standards.
• It monitors the health of AlloyDB, Bigtable, Cloud SQL (MySQL, PostgreSQL, and SQL Server), Firestore, Memorystore for Redis and Redis Cluster, Spanner, and Oracle Database@Google Cloud. For BigQuery it only shows inventory.
• The data isn't real time: it usually updates within minutes, but can take up to 24 hours.

Exam scenarios

Common exam traps