How to move the Azure sql database across the Azure subscription

Question

Use case-How i can move the azure sql database (or Azure sql server) across the azure subscription ensuring the private link of that database a, vnet of the azure sql database and all the other attributes of the Azure sql database remains AS-IS after the movement of the azure sql database to the new subscription

Answer 1

Hi Rahul •,

Welcome to Microsoft Q&A forum.

As I understand, you want to move Azure SQL Database to a different subscription.

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.

Note

Database copy using T-SQL isn't supported when connecting to the destination server over a private endpoint. If a private endpoint is configured but public network access is allowed, database copy is supported when connected to the destination server from a public IP address using SQL authentication. Once the copy operation completes, public access can be denied.

--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;

Refer: https://learn.microsoft.com/en-us/azure/azure-sql/database/database-copy?view=azuresql&tabs=azure-powershell

Let us know if this helps or you have queries.

Thanks