How to copy a database between subscription

I already talked about SQL Package which is a great way to export/import database, we can use that for some purposes like copy database between subscriptions and tenants.

In this post I want to share another way with T-SQL command that also Microsoft explains that here perfectly,  Copy a database - Azure SQL Database | Microsoft Learn I do explains this again as I did try this in my lab and I got a few cases related to copy database in last few weeks, I thought it could be useful for some users.

Before this I want to mention that this process is done via source database and it could impact the source database, if you have a huge database in production I recommended to use this in out working hours.

The whole process is very simple, you need to create same user account in source and destination server, here is my user. Like other examples I deleted those databases and user accounts from my lab. I only used that for testing purpose.

1- Create an SQL Login in source

 
Use Master 
CREATE LOGIN [UserForCopy] WITH PASSWORD = 'RTA20181013@@'
GO
CREATE USER [UserForCopy] FOR LOGIN [UserForCopy] WITH DEFAULT_SCHEMA=[dbo];
GO
ALTER ROLE dbmanager ADD MEMBER UserForCopy;
GO

Run the following in Source database

 
CREATE USER [UserForCopy] FOR LOGIN [UserForCopy] WITH DEFAULT_SCHEMA=[dbo];
GO
ALTER ROLE db_owner ADD MEMBER UserForCopy;
GO

2- Find SID in Master

Then run the following command in the master database to find SID for the user which you have created.

 
SELECT [sid] FROM sysusers WHERE [name] = 'UserForCopy';

3- Create the user in destination server

Then jump to the destination server and create the login in the master database. As I mentioned before the username and password must be same as what you already have created in the source.

 
CREATE LOGIN [UserForCopy] WITH PASSWORD = 'RTA20181013@@', SID = 0x01060000000000640000000000000000DABF0F5060FF6F40B6525ECA9E2FC4C3
GO
CREATE USER [UserForCopy] FOR LOGIN UserForCopy WITH DEFAULT_SCHEMA=[dbo];
GO
ALTER ROLE dbmanager ADD MEMBER UserForCopy;
GO

4- Copy the database with T-SQL Command

Now you need to login to the destination database with that user which you have created in previous step, please be aware you may need to choose the option button and select “master” database as I explained here.

 
-- CREATE DATABASE new_database_name
-- AS COPY OF source_server_name.source_database_name;

CREATE DATABASE MyNEWDB
AS COPY OF Oct2022.mynewdatabase2

It’s done! After a few minutes (depends of database size and SLO) your database will be ready in destination server!