Migrate Azure Database

25 Feb 2021 9183 views 0 minutes to read Contributors

Migrate Azure Database

 

Migrating an Azure Database is creating a copy of a database. We can create copy database within the same resource group or different resource group.

 

Create a copy within same resource group

 

A database copy is a transactionally consistent snapshot of the source database as of a point in time after the copy request is initiated. You can select the same server or a different server for the copy. Also you can choose to keep the backup redundancy, service tier and compute size of the source database, or use a different backup storage redundancy and/or compute size within the same or a different service tier. After the copy is complete, it becomes a fully functional, independent database. The logins, users, and permissions in the copied database are managed independently from the source database. 

 To copy a database by using the Azure portal, open the page for your database, and then click Copy.

 

Now click on Review + Create to create the copy of database, we can chose name of database which is the copy of original database

 

 

We can also create a copy of database using below TSQL

 Create Database SampleDB_TsqlCopy

 

as copy of SampleDB

 

 

Copy database between Resource Groups

 

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. 

Step# 1

Create login and user in the master database of the source server.

 

CREATE LOGIN ServerUser WITH PASSWORD = 'Dishdba@123'

GO

CREATE USER [ServerUser] FOR LOGIN [ServerUser] WITH DEFAULT_SCHEMA=[dbo]

GO

Step# 2

Create the user in the source database and grant dbowner permission to the database.

 

CREATE USER [ServerUser] FOR LOGIN [ServerUser] WITH DEFAULT_SCHEMA=[dbo]

GO

exec sp_addrolemember 'db_owner','ServerUser'

GO

Step# 3

Capture the SID of the user “loginname” from master database

 

SELECT [sid] FROM sysusers WHERE [name] = 'ServerUser'

 

Step# 4

Connect to Destination server.

Create login and user in the master database, same as of the source server.

 

Create login [ServerUser] with password='Dishdba@123',sid = 0x01060000000000640000000000000000ADAC12B07FC0074DA006CECA1A905D0C

GO

CREATE USER [ServerUser] FOR LOGIN [ServerUser] WITH DEFAULT_SCHEMA=[dbo]

GO

exec sp_addrolemember 'dbmanager',' ServerUser'

GO

 

Step# 5

Execute the copy of database script from the destination server using the credentials created

 

CREATE DATABASE SampleDB_Server

 

AS COPY OF dishazureserver.SaampleDB_test