Create A Transactionally Consistent Clone Of A Database In Azure SQL Database

Introduction

A database copy is a transactionally consistent snapshot of the source database taken at a specific moment in time after the copy request has been sent. For the copy, you can use the same or a different server. You can also preserve the source database's backup redundancy, service tier, and compute size, or change the backup storage redundancy and/or compute size within the same or another service tier. It becomes a fully functional, self-contained database after the copy is finished. The replicated database's logins, users, and permissions are managed separately from the source database.

Logins in the database copy

The same logins can be used on both databases when you replicate a database to the same server. On the new database, the security principal you used to duplicate the database becomes the database owner.

When you copy a database to a new server, the security principal who started the copy process on the target server becomes the new database's owner.

To manage database copies, use Azure RBAC roles and permissions.

To create a database copy, you will need to be in the following roles

• Subscription Owner or
• SQL Server Contributor role or

Custom role on the source and target databases with following permission,

Microsoft.Sql/servers/databases/read Microsoft.Sql/servers/databases/write

To cancel a database copy, you will need to be in the following roles,

• Subscription Owner or
• SQL Server Contributor role or

Custom role on the source and target databases with following permission,

Microsoft.Sql/servers/databases/read Microsoft.Sql/servers/databases/write

To manage database copy using the Azure portal, you will also need the following permissions,

Microsoft.Resources/subscriptions/resources/read Microsoft.Resources/subscriptions/resources/write Microsoft.Resources/deployments/read Microsoft.Resources/deployments/write 
Microsoft.Resources/deployments/operationstatuses/read

If you want to see the operations under deployments in the resource group on the portal, operations across multiple resource providers including SQL operations, you will need these additional permissions:

Microsoft.Resources/subscriptions/resourcegroups/deployments/operations/read 
Microsoft.Resources/subscriptions/resourcegroups/deployments/operationstatuses/read

We already have an Azure SQL Database, but we need an exact replica of it.

In this case, I'll demonstrate how the task is completed.

Step 1

To copy a database by using the Azure portal - Navigate to SQL Database,

Step 2

Select the SQL Database – Click Copy

Step 3

Select the Database Name

Select the Database Server

Select Backup Storage Redundancy

Review + Create

Note
The permissions of all users in the new database are the same as they were in the old database. The database owner of the new database is the user who initiated the database copy. Only the database owner can log in to the new database after the copying is complete and before other users are remapped.

Summary

We learned how to copy a transactionally consistent copy of a database in Azure SQL Database in this tutorial. Please leave a comment in the comment box if you have any questions.