Copying an Azure SQL Database3 min read

Copying an Azure SQL Database is a vital skill when managing cloud databases. Recently, a request was received from the “business”. They wanted to create a copy of an Azure SQL Database that was in a development environment. The database has been certified and early testing was accepted. They now want an exact copy in QA to start integration testing. The process of making an Azure SQL Database copy is straightforward. There are several different ways to perform this action.

Two methods chosen will use the Azure Portal and PowerShell to demonstrate the completion of this request.

Azure Portal

First step, login to the Azure Portal and navigate to the source database to be copied.

Click the Copy button to initiate the Create SQL Database – Copy database blade.

Populate the information to be used for your database copy.

The Subscription, Resource Group, and Source Database details are all read-only and cannot be changed. So, start with the Database details for the copy settings.

1. Database Name = CopyDatabase_QA
2. Server = Same Server as the Source
3. Use SQL Elastic Pool = No
4. Compute + Storage = Standard S3 100 DTUs / 250GB Storage

After filling in all the settings, click Review and Create. Once all the settings are validated and found to be correct, click Create.

The Azure Portal will begin the deployment process and show you the steps as they are completed.

When the resource is provisioned you will see the “Deployment Complete” message. Click on Go to Resource to be taken to the new Azure SQL Database.

PowerShell

As always, showing the reader multiple ways to accomplish a task is a high priority. This time let’s review how to perform the same steps using the PowerShell Azure Module.

This first snippet of code will import the Az module and then will prompt you to connect your Azure account. Next, entering your subscription information and then switching your context to the subscription chosen so you have access to those resources.

Check if there are any Azure SQL Databases on the server you are targeting.

This command will return the one development database that is being used as the source database.

Let us now duplicate the source database using PowerShell.

You can see we successfully made a copy of the source database. Using the New-AzSqlDatabseCopy command gives you the ability to create a new Azure SQL Database based on the source database.

Open SSMS and connect to your Azure SQL Database server and expand the Databases folder. Notice there is now a QA database created by our PowerShell command.

 

After performing the steps to copy an Azure SQL Database, the QA database is online and ready for use. One final note, depending on your security configuration you may need to add Users or Service Accounts with the appropriate permissions to the QA database.

If you found this blog handy, please use the Subscribe section at the top of the screen to join my newly created mailing list.  I hope to start delivering content via the mailing list soon. 👆