CODEWITHSUNDEEP
sql-serverazure-sql-database
awj
awj

Reputation: 7959

Unable to access Azure SQL copied DB using same credentials

I'm automating the setup of an Azure SQL DB, copied from an existing DB. This is done programatically from a .NET application using the following query:

CREATE DATABASE [my_db_copy] AS COPY OF [my_db];

The DB is copied on the same server as the source DB.

Once created I use the same connection string (but with the Initial Catalog now changed to my_db_copy) to access the DB. At this point I encounter...

SqlException: Cannot open database "my_db_copy" requested by the login. The login failed.

I can see the copied DB in SSMS but if I try to run any query on it (while logged-in as the same user which created the DB)...

enter image description here

As I've read on this page...

All users in the new database retain the permissions that they had in the source database. The user who initiated the database copy becomes the database owner of the new database. After the copying succeeds and before other users are remapped, only the database owner can log in to the new database.

My interpretation of that statement is that I should be able to access the copied DB using the same login with which I created the copied DB. But I can't.

What else do I need to do?

UPDATE
As @mauridb says in his answer, the DB is not ready, despite the SQL query returning (and the docs say "The T-SQL statement continues running until the database copy operation is complete." which implies that once the statement returns the DB is ready). However, querying sys.dm_operation_status shows

operation = CREATE DATABASE COPY

and

status = COMPLETE

but the DB is still not accessible.

In the end I had to run a a simply query in a loop to pool for the DB being accessible. I found that it takes approximately 1 minute after the query returns before a connection may be opened on the DB.

Upvotes: 0

Views: 273

Answers (1)

mauridb
mauridb

Reputation: 1569

It seems that you are trying to access the database while it is not available yet. You can check the status by querying sys.dm_operation_status in the master database.

https://learn.microsoft.com/en-us/azure/azure-sql/database/database-copy?tabs=azure-powershell#monitor-the-progress-of-the-copying-operation

Upvotes: 2

Related Questions