How to Copy Azure SQL Database to another Subscription ?

Sid_1805 91

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!

Micheal Shank 1 Reputation point

2023-06-02T15:23:44.2466667+00:00
CREATE DATABASE [DestinationDatabase] AS COPY OF [SourceSQL].[SourceDB]

does not seem to work for large databases as the command times out. Large in this sense, being any database where a restore in the Azure portal takes more than a few minutes.

One way to copy a SQL database between subscriptions and logical SQL Servers is below.

Create a new logical SQL Server to act as a transfer SQL Server. The new SQL Server is created in the same subscription and RG as the source SQL Server. Think of this transfer (temporary) SQL Server as being used much like a storage account that might be used to contain a DACPAC. The transfer SQL Server is also a bit like a shopping cart that is used to push a database to some new subscription.

Restore/Copy the source database to the transfer SQL Server.

Move the transfer SQL Server to the destination subscription and sub.

Restore/Copy the transferred database to the destination SQL Server.

Delete the database on the transfer SQL Server. After the copy from the transfer SQL Server to the destination SQL Server, promptly delete the database on the transfer SQL Server to avoid the cost of an idle database on that transfer SQL Server. The deleted database may stick around for a week on the transfer SQL Server. Before the transfer database is deleted, you could configure the Long-Term Retention to keep the transfer and deleted database around for months, or more, if desired. The cost of a deleted database is likely comparable to a DACPAC sitting on a storage account.

If desired, delete the transfer SQL Server... OR keep it around for next time. A SQL Server with 0 databases has a cost of 0 as well. Once a transfer SQL Server is created, it can be moved between source and destination subscriptions as needed to get databases copied around as needed.

6 answers

Oury Ba-MSFT 20,911 Reputation points Microsoft Employee Moderator

2023-09-27T00:34:21.2666667+00:00
Swathi Sudheer

Could you please try the below and let us know if that works.

In SSMS, connect to both servers in a different tenant.

Once you are connected, click on the database name where you want to coly the objects from by selecting select "Tasks" > "Generate Scripts". In the "Generate Scripts" wizard, select the objects you want to copy and choose the "Script entire database and all database objects" option. Then, choose the output format for the script (e.g., SQL file) and save the script to a file.

Modify the script: After generating the script, you can modify it to remove any references to the data in the database. You can do this by removing any INSERT, UPDATE, or DELETE statements from the script if needed.

Execute the script on the target tenant: Once you have modified the script, you can execute it on the target tenant to create the database schema and objects. You can do this by connecting to the target tenant in SSMS or ADS and running the script.

By following these steps, you can copy the objects of an Azure SQL Database from one tenant to another tenant without copying the data. Keep in mind that this method only copies the database schema and objects and does not copy any data or user permissions. You will need to manually recreate any data and user permissions on the target tenant.

Hope that helps.

Regards,

Oury
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to Copy Azure SQL Database to another Subscription ?

6 answers

Your answer