CREATE DATABASE AS COPY does not work with Azure

Question

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.

score 10 · Accepted Answer · answered Aug 31 '16 at 16:35

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;

score 1 · Answer 2 · answered Aug 27 '19 at 07:05

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

score 0 · Answer 3 · edited Jul 27 '19 at 10:37

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]
      ,[resource_type]
      ,[resource_type_desc]
      ,[major_resource_id]
      ,[minor_resource_id]
      ,[operation]
      ,[state]
      ,[state_desc]
      ,[percent_complete]
      ,[error_code]
      ,[error_desc]
      ,[error_severity]
      ,[error_state]
      ,[start_time]
      ,[last_modify_time]
  FROM [sys].[dm_operation_status]

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

CREATE DATABASE AS COPY does not work with Azure

3 Answers3