Create copy of Azure SQL Database using SQL Server Management Studio

Question

When running the following query on an Azure SQL Database "Master" in SQL Server Management Studio:

CREATE DATABASE myDbBackup AS COPY OF myDb;

The following error is produced:

Msg 40818, Level 16, State 1, Line 0
Operation on database 'myDb' failed because there are alter operations pending on the database. Try again after the pending operations have completed.

Any guidance on how to resolve this and end all alter operations that are pending so I can go through with the copy? There is only one other person using this DB and neither he nor I are running a query. I even went so far as to revoke the firewall permissions to the server.. not even this worked. Any thoughts?

Answer 1

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.

• Joshua Robinson: sys.dm_operation_status has AAC670E5-0F17-4CB3-B05C-08CC135ED6F8 0 Database myDb FA789ED6-2480-47C1-B1FD-5CCEBC7EDA15 CREATE DATABASE COPY 3 FAILED 100 40818 Operation on database 'myDb' failed because there are alter operations pending on the database. Try again after the pending operations have completed. 16 0 2014年12月13日 10:07:14.097 2014年12月13日 10:07:31.280 and sys.dm_database_copies is empty.