In this article, we will learn cloning an Azure SQL Database.
In a typical software development lifecycle, when a solution is promoted from one environment to another, from
non-production environments to production environments, there is often a need to create an identical copy of
databases within and across environments. Cloning a database is one of the easiest ways to get the data as well as
database objects from the desired environment. On Azure cloud, SQL Server Databases are one of the mainstream
sources for hosting transactional data, and with it comes a need to move this data as well as database objects
contained in the database on Azure by cloning the database.
Let’s go ahead and see how we can clone a SQL Server database on Azure.
Creating a Source Azure SQL Database
It is assumed that you have an Azure account with required access to Azure SQL Server and Azure SQL Database
services. To start with, we need an existing Azure SQL Server and a database that we will consider as the source
database. In this case, for our exercise, we have an existing Azure SQL Server database as shown below. You can use
any existing database or consider creating a new one and populate it with some sample data. This data is already
populated with sample data available from the service itself.
Every Azure SQL Server database needs to be hosted on an Azure SQL Server instance. This database shown above is
hosted on Azure SQL Server instance as shown below. When an Azure SQL Database instance is created, by default full
backups are created every week, and other types of backups are created as regular and scheduled intervals.
To clone a database, we need at least one backup of the source database. You can configure the backup retention
policy from the Manage Backups section by clicking on the Configure retention
button as shown below.
Assuming at least there’s one backup in place, let’s start creating a new database. Navigate to the SQL Database
service and click on the Add button to start creating a new database. You would find a screen as
shown below. Select the relevant details related to the subscription and resource group. Provide a name for the new
database as well as the SQL Server instance on which it would be hosted. Configure the database capacity if required
or continue with the defaults and click on the Networking button. A screen as shown below would appear.
Configure the network details for the endpoint as required and click on the Additional settings button. A screen
would appear as shown below. An easy way to create a clone of an existing Azure SQL Database is by using the backup
of the source database and creating a new database using the same backup. The default selection for existing data
would be None. Select Sample in the Use existing data setting.
Once this setting is selected, you would find the backup of the source database listed in the backup dropdown. In
the below screen, it shows the name of the backup under the SQL Server instance which contains the backup. This is
the backup of the source database. Select this backup.
You may see a message as shown below, depending upon the configuration of your source database instance, informing
you that the compute and storage settings of your source database may be modified for backup compatibility. Click OK
and then click on the Review + Create button. You would be presented with a screen to review all the configuration
details that you would have specified earlier as shown below.
In this case, we are trying to use the backup of the source database and use it as an existing dataset in the new
database that would be created. Click on the Create button to start the creation of the new
database. Once the database is created, navigate to the dashboard page which should look as shown below.
One of the quickest ways to verify that the newly created database contains the database objects and the data from
the source database is by exploring the database using the Query Editor from the console. Click on the Query editor
button and provide the credentials to log on to the database. On the left pane, you should be able to see that all
the sample data from the source database is already available in this database. You can query these database objects
as well to validate the data. This newly created database is an identical clone of the source database as expected.
If you navigate to the SQL Server instance and click on the Manage Backups section, you would find that the backup
of the newly created database is also created, apart from the backup of the source database. This backup would be
updated from time to time based on the backup policies in place. And the same backup can be used as a source to
clone this database as well.
In this way, we can use existing backups of Azure SQL Database to clone databases on Azure SQL Server instances.
In this article, we briefly learned some of the use-cases that may need cloning of databases. We saw how to work
with Azure SQL Database backups and use the same to clone existing databases, as well as manage the backup retention
policies so that backups are updated as per the desired frequency.