In this article, we will learn cloning an Azure SQL Database.
Introduction
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.
Conclusion
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.
He has worked internationally with Fortune 500 clients in various sectors and is a passionate author.
View all posts by Rahul Mehta
- Finding Duplicates in SQL - February 7, 2024
- MySQL substring uses with examples - October 14, 2023
- MySQL group_concat() function overview - March 28, 2023