SQL SERVER – Creating a Copy of Database in Azure SQL DB

Recently I was about to undertake an interesting assignment for one of my clients who pinged me for Performance Tuning exercise. Since this is something I have been doing full time in the past couple of months, I got into the call immediately. Lesser did I know what I was about to get into actually. Since the problem was about SQL Server query performance, I thought it would be the usual routine. Let us learn about how to create a copy of the database in Azure SQL DB.

On getting over the initial call on Skype, I got to know it was an Azure SQL Database – which in this case didn’t matter because they had a badly written query. Generally, the optimizations that happen are common irrespective of where the database is. Coming back to the context of this blog – the customer was troubleshooting this on production database and requested that we can work off a copy of the database.

The DBA was asking me about how they can take a backup and restore process. Since this was Azure SQL DB, they were not sure of the process. I asked if they were aware of the Export-Import wizard? As you can see below, the database on an Azure SQL DB would look a little different.

Best part of working with Azure SQL databases is the fact that we can use TSQL command to actually create a copy of any database. A typical query would look like:

CREATE DATABASE [new_sqlauth] AS COPY OF [sqlauth-demo].[sqlauth-DemoDB]
GO

In this case I have taken a demo database and created an exact copy.

This was so seamless that I had that database for playing / testing for the duration of troubleshooting. Once I was done with the exercise in about a couple of hours, the team just dropped the database using a standard syntax.

-- Clean up script
DROP DATABASE [new_sqlauth]
GO

For curiosity sake, I was seeing the DBA wants to try the same thing on an on premise SQL Server. This will yield in a syntax error as shown below:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘copy’.

Earlier, I used to say the on premise SQL Server had many cool features which were evidently missing on Azure SQL Databases. But I might need to revisit that statement because the more I start exploring and work on Azure, there are some interesting capabilities that surprise me every single day.

Do let me know if you are working on Azure SQL DB. What have been your experiences? Tell me via your comments below.

Reference: Pinal Dave (https://blog.sqlauthority.com)