Copy Azure SQL Database Deep-dive
If you are supporting Azure you might have to copy Azure SQL Database, In-fact sometimes this becomes a daily task at big\dynamic environments. So before its too late, let’s learn the process inside-out to perform this confidently. (In-case you’re just a beginner and don’t know how to create\provision Azure SQL Database kindly refer this post) To migrate also you need to use Copy command only.
How Copy actually works under the hood?
The copy process uses CREATE DATABASE command and internally under the hood it takes a tail-log backup which is then used in conjunction of Full, Differential and Transaction Log automated backups. This command just initiates the process which is asynchronous in nature.
What’s the T-SQL statement to copy Azure SQL Database?
on same server:
If you opt to create a copy of your database using T-SQL, the statement is pretty simple, here is the syntax and one example for the same:
CREATE DATABASE <New_DB_Name> AS COPY OF <Source_DB_Name>;
here is one example:
CREATE DATABASE SQLChampdb1_UAT AS COPY OF SQLChampdb1;
on a different server:
In case you want to create copy of the database on a different server then in that case you need to use the below mentioned statement and execute it after connecting to master DB of destination server, the statement is like this:
CREATE DATABASE Database1_copy AS COPY OF server1.Database1;
What all permissions do I need in order to copy Azure SQL Database?
To successfully copy a database, you must be a member of DBManager role. You can also achieve the same task if you are logged-in using the login that created the database, if that login id is also not there server level principal ID anyways has all the required permissions to copy any database on a particular server.
What happens to the service tiers of newly created DB?
Since we are copying database, it selects the same service tier which is there on source DB. Be cautious while making copy of a database with premium service tiers because the performance requirements may not be same & this will directly affect your billing. $$ 🙂
why my ID is listed as DBO?
The default behavior is that the login ID which initiates the COPY DB process becomes the database owner of the newly created copy database. You may change it once the database becomes ONLINE.
How to monitor the copy progress of database and how to find when its completed?
Azure provides these two catalog views which can help you monitor the database copy process: sys.databases and sys.dm_database_copies. While the copy is in progress the state_desc column value of sys.databases shows COPYING. The other view sys.dm_database_copies view only exists in master DB and you get rows for source and destination DB both while copy process is in progress, once the process completes the rows are removed automatically.
Is it possible to cancel the copy process?
Yes, you can easily cancel the copy process by executing a DROP DATABASE statement on the new DB. You may also choose to execute it on the source DB {just to clarify the statement would be DROP DATABASE <NEW_DB_NAME> and by executing on source DB means you execute after connecting to source Database 😉 }
Troubleshooting: Why the state of newly copied database is suspect?
This happens if the copy fails for any reason, the moment copy operation fails the state of the Database gets changed to SUSPECT mode. You can find the reason in catalog view sys.dm_database_copies there are a couple of columns for the same: error_code, error_desc, error_severity & error_state.
In case you face this situation you must drop the database and retry the copy operation.
Troubleshooting: How to fix orphan users\re-map users
You won’t face this situation if you choose to migrate or copy Azure SQL Database on the same logical server. But when you copy the DB on another server, it only carries the contained users, database users and its permissions without login IDs. This happens to users which are based on logins. Obviously the users won’t be able to login because either the logins don’t exist or the SIDs won’t match. So in order to fix this you must use the ALTER USER statements to remap the users with logins on the destination server. I’ll shortly write a dedicated blog on this topic.
How to migrate or copy Azure SQL Database using portal?
Copying the DB using Azure portal is very simple & easy but in case someone still needs assistance, here are the screen shots of the same: (I am pasting screenshots from both New and Old Portal)
New portal:
Old portal:
Just login on the old Classic portal and select the Azure SQL Database (the one which you want to copy) and look at the bottom of the frame (portal) there you’ll find an option to “copy” – select the option.
Step -2 to Copy Azure SQL Database.
Happy Learning!
feel free to leave a comment. 🙂