I'm new with T-SQL and MSSQL but need to copy Azure SQL database from one server to another.

As I googled here - it can be done with a CREATE DATABASE Database1_copy AS COPY OF server1.Database1; query, but in my Vusial Studio Code editor on the Ubuntu Linux with the vscode-mssql extention - I have an error:

Msg 156, Level 15, State 1, Line 1 : Incorrect syntax near the keyword 'database'.

My full query looks next:

CREATE DATABASE Database1_copy AS COPY OF oldserver.database.windows.net.olddatabasenamehere;

Additional googling leads me to the same solutions (here is another example I found).

What I'm doing wrong here?

I understood that even the first link published on Azure's resource (azure.microsoft.com) - it does not necessarily is working solution for Azure's SQL.

P.S. Idea is to automate DEV environment rollout (with ARM templates) as a copy of the current Live environment, with creating databases as a copy of Live's databases.

3 Answers 3


From BOL :

The AS COPY OF argument does not support the fully qualified unique domain names. In other words, if your server's fully qualified domain name is serverName.database.windows.net, use only serverName during database copy.

So your command should be

CREATE DATABASE Database1_copy AS COPY OF oldserver.olddatabasenamehere;
  • Thanks - this works. A bit weird for me. First I added oldserverentry to my /etc/hosts file and tried to connect to - but got a message like "The remote partner server name 'oldserverentry.database.windows.net' could not be resolved.". Not sure where this replacement was done. Nevertheless - I successfully connected with oldserver.olddatabasenamehere;. Although now I have "Partner server 'oldserver' is not compatible with server" error, but that's another issue (hello, Azure).
    – setevoy
    Commented Sep 1, 2016 at 7:32

Apart from running:

CREATE DATABASE mydbcopy AS COPY OF server1.mydb

which is a great command to know (my thanks goes to Kbdavis07 and Kin Shah).

One must remember to also run

CREATE USER [mydbcopy-developers] from external provider 
ALTER ROLE db_owner ADD MEMBER [mydbcopy-developers]

to ensure that other AAD users can access the database.
SQL Logins also become orphaned, and they can be fixed like this:

ALTER USER mySQLlogin WITH Login = mySQLlogin

if my sql login was called mySQLlogin, and already existed in the Master database.
The information can be found here, if you look hard enough: https://learn.microsoft.com/en-us/azure/sql-database/sql-database-copy


You need to enclose server and database name in []

CREATE DATABASE Database1_copy AS COPY OF [oldserver].[olddatabasenamehere];

Otherwise it won't work.

You can also check the status by running this:

    SELECT TOP (1000) [session_activity_id]
  FROM [sys].[dm_operation_status]

All from your Master Database of your "New" Target Server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.