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)
Hi, I did try this command but I’m getting error
Incorrect syntax near ‘copy’.
I have already explained that this works only in Azure Database not normal SQL Server which you install
How can we overwrite the database if it is already exist …can you please provide the syntax for that..
I have never tried it. If you find a solution, please share.
Hi, actually i tried to run that query using powershell for Azure Sql Server, but it is still giving the same syntax error. Can you suggest something for this?
the same command do not work if Elastic pool is enabled on the source db. There is alternate approach to restore the db using the portal and powershell. But still looking for tsql way. Share if you come accross this situation.
Msg 40857, Level 16, State 1, Line 4
Elastic pool not found for server: ‘source’, elastic pool name: ‘targetresourcepoolname’.
Please share if you find a solution. you should open support ticket with Microsoft.