What's the best way to create a nightly copy of the production database for tesing

Question

I have a relatively small AzureSQL database, around 200 MB. I am trying to figure out the best way to do a nightly copy to create a Test/Development database.

I have tried various ways to do that. They either error out or time out after 3 hours. This doesn't seem like a very complex task.

Does it have to do with provisioning the database a development rather than production? This database is only used by 2-3 people at a time, so what is the best provisioning level, and it I provision in a more production configuration, will that speed up the copying process? What is the most straightforward method to accomplish this nightly copy?

Answer 1

Hi @Charles Richardson Welcome to Microsoft Q&A thanks for posting your question.

To create a Test/Development database from your AzureSQL database, you can use the Azure SQL Database Import/Export service. This service allows you to create a copy of your database in a different server or in the same server with a different name.

Regarding the provisioning level, it depends on your requirements. If you want to create a Test/Development database for testing purposes only, you can provision it with a lower tier than your production database. This will save you some costs. However, if you want to create a Test/Development database that is similar to your production database, you can provision it with the same tier as your production database.

To speed up the copying process, you can provision your Test/Development database with a higher tier than your production database. This will give you more resources to complete the copy process faster.

Here are the steps to create a copy of your AzureSQL database using the Azure SQL Database Import/Export service:

Export your AzureSQL database to a BACPAC file. You can do this using the Export feature in the Azure portal or using the SqlPackage.exe command-line tool.

Copy the BACPAC file to a storage account in Azure.

Create a new AzureSQL database in the same server or a different server.

Import the BACPAC file into the new AzureSQL database. You can do this using the Import feature in the Azure portal or using the SqlPackage.exe command-line tool.

You can schedule this process to run nightly using Azure Automation or Azure Functions.

Regards

Geetha