Sql-server – CREATE DATABASE AS COPY does not work with Azure

azureazure-sql-databasesql server

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.

Best Answer

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;

Related Solutions

Sql-server – Help in Automating Database Restoration in SQL Server

Assuming the row in the config table is identified by WHERE DB = @DB:

UPDATE msdb.dbo.db_restconfig 
SET FilGroups_Move_To_FullQuery = STUFF((SELECT ',MOVE N''' + LogicalName 
   +  '''TO N''' + LEFT(physicalname, CHARINDEX('.', REVERSE(physicalname)) - 1)  
   + @path_name 
   + REVERSE(LEFT(REVERSE(physicalname),CHARINDEX('\', REVERSE(physicalname), 1) - 1))  
   + ''''
 FROM msdb.dbo.restorelist_files
 FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)'),1,1,'')
WHERE DB = @DB;

Sql-server – Create copy of Azure SQL Database using SQL Server Management Studio

Try reviewing DMVs sys.dm_database_copies and sys.dm_operations_status which are Azure SQL Database-only, and meant for reviewing progress on these type of operations eg

SELECT *
FROM sys.dm_operation_status

SELECT *
FROM sys.dm_database_copies

Review the state_desc and error_desc columns. Post the output here if required.

Best Answer

Related Solutions

Sql-server – Help in Automating Database Restoration in SQL Server

Sql-server – Create copy of Azure SQL Database using SQL Server Management Studio

Related Question