5

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.

6

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;
| improve this answer | |
  • Thanks - this works. A bit weird for me. First I added 111.111.222.222 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 Sep 1 '16 at 7:32
1

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://docs.microsoft.com/en-us/azure/sql-database/sql-database-copy

| improve this answer | |
-1

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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