GCP Cloud SQL — Recovering an accidentally deleted database/Cloud SQL Instance
GCP Cloud SQL is a fully-managed relational database service for MySQL, PostgreSQL, and SQL server.
GCP Cloud SQL supports instance backups of some kind. They make it really easy to create and delete databases from the CloudSQL instance details -> Database tab.
The concern is about how easy it is to delete a database, especially since mistakes happen and to make it worse when you delete a database/instance, all its backups are also being deleted. Backups are strongly bounded to the database resource. Is it possible at all to restore an accidentally deleted database/instance? There are no docs on how one would go about such a scenario. Or better yet, how to prevent such accidental deletes.
What to do to resolve the situation
Even when Google says backups are deleted when the instance is deleted, don't give up. The GCP web UI gives no chances to recover, so the only way is by using the gcloud CLI commands.
Backups should still be somewhere even when the docs say they shouldn't given you are quick enough to check them when accidental deletion occurs. Provided you know the name of the deleted SQL instance, then running this next command should list all the backups of instance.
gcloud sql backups list --instance=deleted-instance-name --project project_id
If you are lucky and quick enough then you should see your backups listed as above.
The next step is to restore the backups. You need to create a new instance with the same configurations as the previous one to be able to restore the instance.
After creating the instance then you can restore the instance with this command:
$ gcloud sql backups restore [BACKUP_ID] --restore-instance=[NEW_INSTANCE] --project [PROJECT_ID]--backup-instance=[DELETED_INSTANCE]
Finally, even when restore works, you still need to verify all the data was there. If everything is okay then you are good to go to next and final step. Next and final step is to perform a SQL-dump in order to ensure you have a recent copy of the instance.