I saw a question posted recently on what data is included in a full SQL Server database backup. I hadn't seen that question in some time, but the post was a good reminder that this is not an intuitive concept, and new data professionals might not understand how a full backup works. If you don't know, you should do a little research (and write a #SQLNewBlogger post for yourself).

The way a SQL Server backup works, either with an on-premises install or the Azure SQL Database version, is well known and documented. Even if you can't make a "normal full backup" in Azure SQL Database, the process is the same. You don't have to run the backup, as Azure does that for you, but you can specify a restore and understand which data will be available in your restored database.

Cosmos DB is a different type of data store, existing only in Azure and storing non-relational data. The service has been promoted quite a bit, and some of you might even be using it. Do you understand how backups, and more importantly, how restores work?

I ran across an article that discusses the way Cosmos DB continuous backup works. This process isn't quite what I'd expect. Changes are backed up locally (either LRS or ZRS), which makes sense. However, all changes (called mutations for some reason) are backed up within 100s, asynchronously. That's good, and it's not perfect, but it's pretty good. What's more, you can restore a container, a database, or the entire account. That matches up closely with what I expect in Azure SQL, including the need to restore into a new account. What isn't great is that stored procedures, triggers, and UDFs aren't restored.

As with any sort of backup and restore operation, you should be sure you understand the way operations occur, the impact of restores, and the costs involved. You get charged for backup space and restores. Maybe the most important thing to know is how to perform a restore. If you have a problem, you want to be sure that you not only know the mechanics of restoring data but how to reconcile any potential changes between the old and new database, as well as how to ensure all other objects (stored procs, etc.) are put back in place and clients are directed to the correct database.

This process might not be as simple as MS Docs describes, and certainly, I've found SQL Server restores are not always as simple as we might like. Practice ahead of time and be sure you can recover a system in a way that meets your clients' needs.