How to Create On-Demand and Automatic Backups of Cloud SQL Instances

If you have a good backup strategy, you can easily restore data if your GCP Cloud SQL instances start having issues. Unlike exports (another restoration option), Cloud SQL backups cover the entire database and are managed by Cloud SQL.

You can create Cloud SQL backups automatically if it is important that you have up-to-date data according to your business needs and required point-in-time recovery (PITR), or you can create backups on-demand with a manual process.

In this guide, we’ll show you how to create backups manually and how to schedule automatic backups for your Cloud SQL instances.

Blink Automation: Ensure Cloud SQL Instances are Configured with Automated Backups

Blink + GCP

Try This Automation

How to Create On-Demand Backups

Manual backups are a good practice ahead of performing any kind of risky operation on your database. For example, maybe you are about to migrate database schema.

Instead of waiting for the backup window to capture an automatic backup, you can create an on-demand backup so if the operation goes poorly, you don’t risk losing any data. You can also create on-demand backups if automatic backups are not enabled and it’s been a long time since your last backup for that instance.

Using the Google Cloud Console:

1. Access the Google Cloud Console by logging in
2. After logging in, navigate to the "Cloud SQL Instances" page
3. Click on the name of the instance you wish to create a backup for.
4. You should see the instance's Overview page.
5. From the SQL navigation menu, select Backups.
6. On the Create Backup page, add a description to your backup (optional)
7. On the Create Backup page, click Create to create your backup.

Using the gCloud CLI:

Create the backup in the gCloud CLI using the following command:

gcloud sql backups create \
--async \
--instance=INSTANCE_NAME

You can include --async if the next operation you are running is low risk, and it is a way of allowing your backup to be created in the background while you run other operations. You might not want to include --async if the next operation is risky since your backup might not complete in time.

By default, backups are created in the location of the original instance. To change the desired location for your backup, you can specify it with a parameter:

gcloud sql backups create \
--async \
--instance=INSTANCE_NAME \
--location=BACKUP_LOCATION

And just like that, you’ve created a backup of your specific Cloud SQL instance in a specific location. Now, we’ll look at scheduling your backup creation.

How to Schedule Automatic Backups

Automated backups are a good way to ensure that you always have a recent backup you can use to restore a failing instance.

To schedule automatic backups, you need to set a daily 4-hour window when your  instance is least active. The automatic backup process will start at some point during that window.

Using the Google Cloud Console:

1. Access the Google Cloud Console with your account
2. After logging in, navigate to the Cloud SQL Instances page
3. Find the name of the instance you wish to create backups for
4. Click on that instance's name to open the Overview page
5. From the SQL navigation menu, select Backups and click
6. Next to the Settings option, click on Edit.
7. From here, you will be able to schedule timeframes for your data to be automatically backed up.
8. Save the configurations you have made.

Using the gCloud CLI:

Keep in mind that the backup-start-time uses a 24-hour format and is based in the UTC time zone. Furthermore, the start time is when the 4-hour-long backup window will begin, and the backups can start at any moment within the 4-hour window. You need to edit the instance to have a specific backup start time like so:

gcloud sql instances patch INSTANCE_NAME --backup-start-time=HH:MM

For example, let's say you have a Cloud SQL instance named "DOG" and you wanted to set the back-up-start-time to 1:30 UTC. Your input into the gcloud CLI would be:

gcloud sql instances patch DOG --backup-start-time=01:30

After this, you must confirm the changes you have made. Ensure that you have set the correct backup start time for the correct instance like so:

gcloud sql instances describe INSTANCE_NAME

You can confirm these changes when checking the backupConfiguration section. Make sure that you can see enabled: true along with the backup start time that you specified. This will mean your automated backups have now been scheduled to the time parameters you chose.

Automating Cloud SQL Backups with Blink

Creating or scheduling Cloud SQL backups only takes a few steps, so you can do it manually if you want to.

However, conducting these steps does require context-switching and you either create a backup right away or create them on a regular schedule. What if you want to create backups as a step in a workflow, or only create them under certain conditions?

With Blink, you can create a simple automation like this one that follows these steps above so creating a backup is always a click away.

This automation in the Blink library runs the following steps:

1. Checks whether SQL database instances are set to enable automated backups.
2. Emails the results to a specified email address.

You can embed this automation into workflows so you can ensure backups when they are most needed. This gives you flexibility to optimize your backup storage and customize your process.

Get started with Blink and streamline your Cloud SQL backup process today.