Self-serve database backups and cloning environments

[callumbwhyte]

Our team has recently been battling some rather messy and annoying issues with Cloud, but I hope to make a proposal to make this easier for all...

As part of delivering a project to a client for testing we needed to create a replica of production. We needed a copy of all content, media + files, members, etc. To do this we: deleted our existing dev environment, added a new / fresh dev environment, attempted to use Deploy to restore the environment but it would consistently fail due to large files etc. We reached out to support, were told we'd have to wait a few weeks for an escalation, went back and forth, etc, etc...

I feel this isn't actually a support or platform issue, it's a technology limitation.

Deploy isn't a suitable mechanism for this type of process - inefficient, doesn't work for members etc. We're able to use SSMS to take a backup of the database, but not able to restore that same backup onto Cloud ourselves (the docs on this could be interpreted as this being possible, but it is actually referring to restoring the database elsewhere e.g. locally).

Our solution was to script our database to SQL, drop all tables in our target database, use Big SQL Runner to import our script on the target database, and finally use AZ-Copy to move our media files. It worked well, but this is obviously prone to errors and less than ideal.

I'd like to propose some solutions:

• Provide a checkbox when creating a new environment to clone XYZ environment. You can easily perform a database copy and AZ-Copy for us on the backend.

• Provide a way to easily restore a backup of a database. This could be a file upload within the portal, or an interface to choose from the regular snapshots Azure takes anyway.

Solving this would reduce support burden, required effort on the dev side, and just make everyone much happier ;-)

[dawoe]

Hi @callumbwhyte

We ran into similar issues, but when upgrading a site from V8 to V10 on cloud when following the official migration guide.

In short the steps are :

1. Create a backup of the V8 database and media
2. Point your local clone of the V10 project to that database and run the upgrade
3. Update your code
4. Push to cloud
5. Transfer content

But the last step is the issue, while it works great for content and media we ran into the same issue as you have been experiencing : no members, audit logs, version history, form entries, user group and user configurations, data in custom tables ... were transferred.

So we followed the same approach by scripting the database and running the script on the cloud database and transfer the media to blob storage.

Because the client was still editing content on the V8 site during the upgrade process, we needed to repeat for migrating the database again when we were ready with the upgrade.

This is also an issue when you want to migrate an existing site in to Umbraco Cloud.

As we are going to migrate more sites from V8 to VNext the features you propose would come in handy

Dave

[hfloyd]

I agree - A simple DB copy would be an awesome Cloud option. I do a lot of migrations to Cloud so I am usually working with a fully-functioning local DB, and it can be a long, tedious, and glitchy process to get it all pushed up through Deploy. Deploy is fantastic for working with a site which is already up on Dev and Live, and you need to push individual things back-and-forth between environments, but for a "whole site" transfer.... Ugh.

Also... I just had a project on v8 where they just wanted to move the site from some sub-par hosting to Cloud, but the site was originally developed with hard-coded INT IDs all over the place. Which works fine on a single live environment with unchanging INTS, but totally blows up when transferred via Deploy to Cloud. Since some of the source code is missing, we can't redevelop it properly, so we will have to abandon the project of transferring it to Cloud. If I could have just pushed up the live DB right to the Live environment, the site could have continued to function (live content-editing only, but still...) on Cloud. Now we will have to wait until they have a rebuild project approved for sometime next year.

[c9mb]

Yes, as someone who also routinely works with WPengine and is used to taking environment backups and copying/restoring from them when things go wrong, the difficulty of creating/restoring any type of versioning backups always makes me nervous and causes stress any time a significant change is being implemented. I realise that somewhere there must be nightly backups available to support for a limited period, but having the ability to access these, create snapshots and restore, would not only be practical, but also address awkward questions I get asked during proposal stage when hosted deployment is mentioned. e.g. How do I quickly recover if I realise we've been hacked?

[sajumb]

I must start with a sheepish apology for the delay in responding to these great feature requests. Let's just say we've been marinating your ideas to perfection – hopefully, the wait will prove worth it!

We're excited to dive into the roadmap of upcoming features that aim to significantly enhance the export and import database capabilities in Umbraco Cloud. We will initially consider features surrounding the Umbraco SQL database before adding functionality to restore content/media and git history to accompany the SQL database export. The features listed below are likely to be limited to cloud projects on Standard and Professional plans initially, but we will continuously evaluate which features are relevant for the types and user personas using the cloud project plans.

So, our primary focus in this initial phase is on developing robust Point-In-Time Recovery (PITR) and .bacpac file export functionalities for the SQL database. Here's what this entails:

Point-In-Time Recovery (PITR): This feature will allow you to create a snapshot of your SQL database at a specific point in time. This is particularly useful in scenarios where you need to revert to a previous state due to data corruption, accidental deletion, or other unforeseen issues. PITR will be designed to be intuitive and user-friendly, ensuring you can easily navigate through different versions of your database.

.bacpac File Export: Alongside PITR, we're implementing the .bacpac file export feature. A .bacpac file is a ZIP file with an export of a SQL Server database. This feature will enable you to export your SQL database as a .bacpac file, which can then be imported into any SQL Server or Azure SQL Database. This is especially useful for moving databases between different environments or for backup purposes.

While these export-oriented functionalities are taking shape, we're still in the process of defining the specifics of the import feature. We’ve heard some great use cases from you so far, but we're eager to gather more feedback. If you have any specific needs, challenges, or ideas that haven’t been mentioned yet, please share them with us. Your insights are crucial in helping us develop a feature that truly aligns with your workflows and addresses your pain points.

Regarding content transfer and restore, I just want to highlight the recent import and export feature introduced in Umbraco Deploy which has shown tremendous value for many users and cases already.

Thank you for your patience. Feel free to comment below. We will bring more updates here as the work progresses.

[c9mb]

In no way trying to influence your design (maybe just a bit) but fwiw... here's a screenshot of the WPengine backup function that I use on a regular basis for my WordPress clients. Basically, lets me see both the automatic daily backups as well as custom backups that I can make at any Point-in-Time. Restore is simply - pick a snap-shot, pick a target, confirm & go.

[mclausen]

Howdy Folks,

I think we owe you an update on this issue 🤠

In June, we launched the export database feature, which allows you to take a backup of your database in any environment with the click of a button ✅.

In the documentation, we guide you on how to restore a set database into an existing environment.

If I can loop you in on a secret, we also have an internally managed way to restore a .bacpac file onto a specific environment. This feature, which we use for internal support, will allow us to swap a database directly on any cloud environment. We plan to release this feature publically by August-September, and we hope it will satisfy the initial requirements for this feature.

The database backup is where we want to start, but in the future, we plan to expand to full website backups, including both the website and media. This is something we still need to plan alongside other features.

We hope this satisfies the main proposal for this ticket. If I’m wrong, feel free to reopen or, even better, create a new issue adjusted to the new capabilities.

Kind regards, @mclausen, Cloud Platform/Core Team Lead

Feel free to let me know if you need any further adjustments!