Azure SQL Backup – Move Snapshot from Prod to Dev Recurringly

azure-sql-databasebackup

How can I take an existing production Azure SQL database (or one of its regular backups) and restore it to a dev environment on a regular basis? I want to give developers the option to work on their regular dev database but also have a snapshot of prod available in order to safely reproduce issues found on production. And optionally, to be able to replace our regular dev database with one of these snapshots manually, if and when we choose to.

Right now, I can create a new database with a new name from one of the built in regular backups from prod, but then I have to update all of the connection strings each time. If I can have a few to swap between then I can use deployment slots to keep copies of the configs, so I could have a 1, 2 and 3 database and shuffle between then or something. Any help would be greatly appreciated! Thanks!

Best Answer

You can use CREATE DATABASE AS COPY to create a snapshot of production database, and then use the same CREATE DATABASE AS COPY option to create databases for development environments using the snapshot database. All this can be automated using an Azure Runbook. The runbook can run with a SQL login with elevated privileges to accomplish all copy activities. 

CREATE DATABASE db_copy   
    AS COPY OF ozabzw7545.db_original ( SERVICE_OBJECTIVE = 'P2' );

Adopting Azure Web Apps may help you, because of their ability to override application settings and connection strings from within the Azure Portal. This way you could allow your developers or infrastructure admins to deploy new code to the Azure Web App without letting them even see the appSettings and Connection Strings that particular environment uses.

Visual Studio Team Services resource groups and Microsoft Release Management Agents may be very useful for you.

Related Question