Microsoft Azure – Restore to a Point in Time with Azure SQL Database

Pre-requisite: Azure

Microsoft Azure SQL Database is a service that provides managed Paas service running the latest SQL Server Database. It comes with a feature of point-in-time backups. So, in this article, we will study how we can create a point-in-time backup using Azure SQL Database and restore it using created backup. We will see two different methods to restore the database. We are going to use the Azure portal and Azure CLI for restoring our database.  So, let’s restore Azure SQL Database.

Azure SQL Database: Azure SQL Database is a standard service that provides managed PaaS database servers.

Point-In-Time Restore: In point-in-time restore, we restore the database to the state at a particular date and time which was backup at the server.

Steps to Restore Azure SQL Database Point in Time

Using Azure Portal 

Step 1: For Restoring the database using the Azure portal visit the database overview page from the portal. When you create Azure SQL Database it automatically generates point-in-time backups every 24 hours.  

 

Step 2: Click on Restore in the upper menu it should take you to create the SQL Database-Restore database page.

• Fill in the details according to your account. In the Source Details pane, your database name will be selected automatically. In select Source select “Point-in-time”.
• Select restore point as current DateTime. this will confirm that the restore point will be selected between the earliest restore point and the current datetime.

 

Step 3: Now Fill in the Database details. A new database will be created after restoration by Azure. Type the name for the new database and select your desired Storage. Keep other things as default and click “Review+create” . and on the next page click “Create”.

 

Step 4: After successful completion, a new database will be created with the name given on the same server. This database will have restored data.

• You can see the new database on the server overview page under Available Resources. Now you can use a new database with restored data.

 

Using Azure CLI

Step 1: First install Azure CLI on your machine and configure it with your account info. Now you can access your resources using Azure CLI. Open Command Prompt/Terminal and enter the below command to see available databases.

• We have to provide the resource group name and server name as arguments to the command.

az sql db list --resource-group <YOUR-RG-NAME> --server <YOUR-SERVER-NAME> 

 

Step 2:  Now to restore the database we have to run the below command. We have to provide the name of the destination database which is the required argument. Other optional arguments can also be provided. time argument can be provided to mention the current time.

az sql db restore --dest-name <NAME-OF-DESTINATION-DATABASE> --edition <DB-EDITION>
 --name <DB-NAME> --resource-group <YOUR-RESOURCE-GROUP> --server <YOUR-SERVER-NAME> 
 --subscription <YOUR-SUBSCRIPTION> --time <CURRENT-TIME-STRING>

 

Step 3: After running the above command, a new database will be created with restored data. You will see the following output in the terminal.

 

Step 4: You can run the command in Step 1 to see available databases. Also, you can see the new database on the server overview page on the Azure Portal.