A simple way to automate On-demand Backups for Cloud SQL
Tags: cloudsql, googlecloud, cloudscheduler
Published: 2021-06-04 18:08:12

---
title: A simple way to automate On-demand Backups for Cloud SQL
published: true
date: 2021-05-15 21:40:11 UTC
tags: cloudsql,googlecloud,cloudscheduler
canonical_url:
---

![](https://cdn-images-1.medium.com/max/1024/1*xvO389jnT-RsVLHMLQnqBg.png)

---

Cloud SQL offers two types of backups: [on-demand](https://cloud.google.com/sql/docs/mysql/backup-recovery/backups#on-demand-backups) and [automated](https://cloud.google.com/sql/docs/mysql/backup-recovery/backups#automated-backups). While enabled automated backups occur once a day and cannot be scheduled for shorter intervals, you can create the on-demand ones at any time. This article will show you how to automate the on-demand backups to be created every X hours in the simplest way possible. For that, I will be using the [Cloud Scheduler](https://cloud.google.com/scheduler).

### I: Create a Custom Role that will allow creating Cloud SQL backups

You need the following [permission](https://cloud.google.com/sql/docs/mysql/project-access-control#permissions_and_their_roles) to create Cloud SQL backups:

- cloudsql.backupRuns.create

This permission should be granted to the service account that will be used by Cloud Scheduler as identity.

You could simply grant the service account one of the predefined roles, such as **Cloud SQL Editor** or **Cloud SQL Admin** , however, following the principle of least privilege, I encourage you to create a [custom role](https://cloud.google.com/iam/docs/creating-custom-roles) instead. Here is how you can do it:

1. In Cloud Console, navigate to **IAM & Admin** -> **Roles** and click “ **CREATE ROLE** ”.
2. Fill in the required fields and add the `cloudsql.backupRuns.create` permission.

In the Cloud Console it will look like this:

![](https://cdn-images-1.medium.com/max/506/1*DKNB7-sJDgyFG2IM5cDVJQ.png)

### II: Create a Service Account for your Cloud Scheduler job

[Create](https://cloud.google.com/iam/docs/creating-managing-service-accounts#creating) a service account giving it the role from the previous step:

![](https://cdn-images-1.medium.com/max/501/1*car3O4R5vzvOmawHfdm8gQ.png)

### III: Create a Cloud Scheduler job

1\. Define the **job** and its **frequency**. I have configured it to run every 6 hours:

![](https://cdn-images-1.medium.com/max/495/1*_Qyv3U1U9-tc-Z68dRIxWg.png)

2\. Select **HTTP** as the target type and provide the following [URL](https://cloud.google.com/sql/docs/mysql/admin-api/rest/v1beta4/backupRuns/insert):

```
https://sqladmin.googleapis.com/sql/v1beta4/projects/{project}/instances/{instance}/backupRuns
```

Remember to replace **{project}** with your project ID, and **{instance}** with your Cloud SQL instance ID. Make sure that the selected HTTP method is **POST**.

3\. For **Auth header** select the “Add OAuth token” option.

4\. Specify the **service account** you have created in Step II.

![](https://cdn-images-1.medium.com/max/494/1*W2CNlsrZ3Y0nAsPafNKULQ.png)

5\. Click “ **CREATE** ”.

### Conclusion

That’s it! Now your Cloud SQL instance will have the backups created every X hours (in my particular example — every 6 hours), and you can modify the frequency easily.

Nevertheless, you should keep in mind the following:

> On-demand backups are not automatically deleted the way automated backups are. They persist until you delete them or until their instance is deleted. Because they are not automatically deleted, on-demand backups can have a long-term effect on your billing charges if you do not delete them.

*Reference: [https://cloud.google.com/sql/docs/mysql/backup-recovery/backups#on-demand-backups](https://cloud.google.com/sql/docs/mysql/backup-recovery/backups#on-demand-backups)*

However, you can address this issue by configuring [Object Lifecycle Management](https://cloud.google.com/storage/docs/lifecycle#conditions) on the GCS bucket storing your backups.