2

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!

3

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.

  • Thanks for the suggestions. I have used the copy operation from the portal with ease before. Running it under Powershell has been a huge pain as I get a lot of random errors about missing modules or dlls even after a lengthy troubleshooting period. I'm sure I can get it to work, but this is definitely not a simple turnkey thing – Dan Csharpster Jan 22 '18 at 17:37
  • I am glad you found something that works on your scenario. – Alberto Morillo Jan 22 '18 at 18:10
1

The solution I think will go with is to give up on preserving the db name of the snapshot. Instead I can use Azure Powershell commands to copy the database, using the command New-AzureRmSqlDatabaseCopy, and I will add some kind of datestamp on the end of the name. Then I will use Set-AzureRmWebApp to update the connection strings with the new Initial Catalog set to the new datestamped backup database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.