How to Copy Azure SQL Database to another Subscription ?

Question

How to Copy Azure SQL Database to another Subscription ?

I do not find specific documentation in Resource mover for Azure SQL. Can someone please re-direct me?

Looking for pre-requites like Private End point enables Dbs, how to move its dependencies, associated central log analytics ( use for logging) to the Azure SQL etc...

thanks!

Answer 1

Good day,

Copy a database to a different subscription is supported in multiple ways including with direct Transact-SQL query

https://learn.microsoft.com/en-us/azure/azure-sql/database/database-copy?tabs=azure-powershell#copy-to-a-different-subscription

The code is from the above link. There are other options - check the original documentation for full explanation and more options

--Step# 1  
--Create login and user in the master database of the source server.  
  
CREATE LOGIN loginname WITH PASSWORD = 'xxxxxxxxx'  
GO  
CREATE USER [loginname] FOR LOGIN [loginname] WITH DEFAULT_SCHEMA=[dbo];  
GO  
ALTER ROLE dbmanager ADD MEMBER loginname;  
GO  
  
--Step# 2  
--Create the user in the source database and grant dbowner permission to the database.  
  
CREATE USER [loginname] FOR LOGIN [loginname] WITH DEFAULT_SCHEMA=[dbo];  
GO  
ALTER ROLE db_owner ADD MEMBER loginname;  
GO  
  
--Step# 3  
--Capture the SID of the user "loginname" from master database  
  
SELECT [sid] FROM sysusers WHERE [name] = 'loginname';  
  
--Step# 4  
--Connect to Destination server.  
--Create login and user in the master database, same as of the source server.  
  
CREATE LOGIN loginname WITH PASSWORD = 'xxxxxxxxx', SID = [SID of loginname login on source server];  
GO  
CREATE USER [loginname] FOR LOGIN [loginname] WITH DEFAULT_SCHEMA=[dbo];  
GO  
ALTER ROLE dbmanager ADD MEMBER loginname;  
GO  
  
--Step# 5  
--Execute the copy of database script from the destination server using the credentials created  
  
CREATE DATABASE new_database_name  
AS COPY OF source_server_name.source_database_name;  

Answer 2

Hi anonymous user ,

some Azure resources can't be moved to a different Azure subscription.
Please check this website for the options to move: https://learn.microsoft.com/en-us/azure/azure-resource-manager/management/move-support-resources
If a resource can't be moved by Azure Resource Mover you need to create them new in the target Azure Subscription.

----------

(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

Answer 3

Hi anonymous user Thank you for posting your Question on Microsoft Q&A.

In addition to @Ronen Ariely 's answer You can use the steps in the Copy a SQL Database to a different server section to copy your database to a server in a different subscription using T-SQL. Make sure you use a login that has the same name and password as the database owner of the source database. Additionally, the login must be a member of the dbmanager role or a server administrator, on both source and target servers.

The Azure portal, PowerShell, and the Azure CLI do not support database copy to a different subscription.

Regards,
Oury

Answer 4

How can we copy the objects of an Azure SQL Database from one tenant to another tenant without copying the data, just the structure?

I was able to copy the database following this copy-to-a-different-subscription documentation from Microsoft using SQL scripts. But this doesn't talk about how we can copy the database without the data.

Does anybody have any suggestion?

Answer 5

Hi @Sid_1805

Here are the couple of options-

Option 1: PITR restore method -

Step 1: Create new server in source subscription
Step 2 : Copy the database from source server to newly created server within the same subscription
step 3 : move the entire server to the destination subscription along with the database
step 4: now again copy the database from moved server between the servers within destination server

Ref: Move server https://techcommunity.microsoft.com/t5/azure-database-support-blog/how-to-move-azure-sql-server-to-another-subscription/ba-p/368790
Ref Copy database: https://learn.microsoft.com/en-us/azure/azure-sql/database/database-copy?tabs=azure-powershell

Option 2: .bacpac restore method / export-import method

You can also use export import method

Step 1: Export the source database in .bacpac format and store it into the storage account / local storage
Step 2 : Import the bacpac into destination server

Ref :
Export - https://learn.microsoft.com/en-us/azure/azure-sql/database/database-export
Import- https://learn.microsoft.com/en-us/azure/azure-sql/database/database-import?tabs=azure-powershell
Blog: https://www.mssqltips.com/sqlservertip/5189/restore-an-azure-sql-database-from-one-server-to-another-server/

Option 3: Direct copy the database to another subscription

Copy the database directly to the different subscription. Please note this method is not supported via azure portal.

Ref: https://learn.microsoft.com/en-us/azure/azure-sql/database/database-copy?tabs=azure-powershell#copy-to-a-different-subscription
QnA: https://learn.microsoft.com/en-us/answers/questions/610126/how-to-copy-azure-sql-database-to-another-subscrip.html

Thank you!