Recently, I had an interesting assignment from one of my clients who needed help with performance tuning in their SQL Server database. Little did I know that this assignment would lead me to discover a powerful feature in Azure SQL DB – the ability to create a copy of a database.

During the initial call, I learned that the client was facing performance issues due to a badly written query. They wanted to troubleshoot the problem on a copy of the production database. However, since they were using Azure SQL DB, they were unsure of the process to create a copy.

I suggested using the Export-Import wizard, but then I realized that Azure SQL DB provides a much simpler way to create a copy of a database – using a TSQL command. Here’s an example of how to create a copy:

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

With just this simple query, I was able to create an exact copy of the database. This copy allowed me to perform all the necessary troubleshooting without affecting the production environment. Once I was done, the team simply dropped the copy using the standard syntax:

-- Clean up script
DROP DATABASE [new_sqlauth]
GO

However, it’s important to note that this method is specific to Azure SQL DB. If you try to use the same syntax on an on-premise SQL Server, you will encounter a syntax error.

Previously, I used to believe that on-premise SQL Server had more advanced features compared to Azure SQL DB. But as I delve deeper into Azure, I am constantly surprised by the capabilities it offers. The ability to create a copy of a database with a simple TSQL command is just one example of the power of Azure SQL DB.

If you are working with Azure SQL DB, I would love to hear about your experiences. Have you encountered any interesting features or challenges? Share your thoughts in the comments below!