Point In Time Restore for Azure SQL Database

Table of Contents

• Introduction
• Automatic Backups
• Backups Retention Period
• Point In Time Database Restoration
• Step by Step process for Point In Time Database Restoration
• Summary

Introduction

In this article, we are going to discuss the point in time restore for Azure SQL database and its backup capabilities. Azure SQL Database is a database as a service offered inside the Microsoft Azure. By database as a service:

1. Azure is responsible for taking care of database software, automatic backups, high availability, operating system & its hardware. User only needs to take care of database itself.
2. User needs to pay according to service usage on an hourly basis. They can make payment monthly or yearly but can’t be forced to pay any amount upfront unless user himself wants to pay.
3. Based on usage demand, user can scale up/down or create/destroy the environment without the provider intervention.

Automatic Backups

As we mentioned above in Point 1, Azure is responsible for automatic backups. Full backup happens once a week, differential backups happens daily and transactional backup happens every 5 mins. Full backup started automatically after the database creation and after that, other backups schedule automatically. This automatic backup service is free of cost.

Backups Retention Period

Retention time period for these database backups is automatically decided on the basis of service tier selected for the database. Retention Time period according to three service tiers is given below:

1. Basic Service Tier - 7 days
2. Standard Service Tier - 35 days
3. Premium Service Tier - 35 days

Point In Time Database Restoration

Point in time restoration is a self-service capability. It enables user to restore a basic, standard or premium database to any time within its service tier retention period. When we go for point in time database restoration, it always creates a new database on the logical SQL Server. Automatic database backup service is free. But database restoration process is not free as it involves creation of new database. This new database is charged at normal database rates. Thus, point in time database restoration is a very low-cost, simple and zero admin process which protects the database from accidental database corruption or accidental data deletion.

Step by Step Process For Point in Time Database Restoration

In this section, I will explain the step by step process of Point in Time database restoration in Azure.

To explain this process, I will use the reference of my Azure SQL database “AzureDBRestoration” as shown in the below screenshot.

Click on the database name link and it will open the property page for the selected database as shown below:

To restore the database, we need to click on the Restore link from the top Menu. It will bring up the Restore page for the selected database as shown in the below screenshot.

Database restoration process always creates a new database with a different name as shown in the above screenshot. Here, the default name for the new database as suggested by the Azure is “AzureDBRestoration_2019-01-07T17-13Z”. We can change it as per our requirement. In this case, I have changed the name to “AzureDBRestoration_Restore” as shown in the below screenshot.

In the above screenshot, suggested time for Restoration point is 5:13:00 PM on 2019-01-07. Time mentioned in the screen is always in UTC. We can change this restoration time as per our need. Now, suppose we need to restore the database using the restore point time of 3 hrs back. So we will change the restore point time to 2019-01-07 2:13:00 PM (UTC) as shown in the below screenshot.

After changing the Restored database name and the restore point time, if required, we can also change the Elastic Pool and Pricing tier. Now click OK. It will start the restoration process which you can see under the notifications as shown in the below screenshot.

Once the database restoration process completed, you will get the success message as shown in below screenshot.

You can also see the newly created Azure SQL database “AzureDBRestoration_Restore” in the database list by clicking on the SQL Databases link present at the left side as shown in the below screenshot.

Summary

This article tried to explain the automatic backups services provided by Azure, Backup retention periods and how to take the point in time database restoration. This database restoration process helps in protecting the database from corruption and more importantly, from accidental deletion of data.

I hope this article will help you in Azure database restoration. Please don’t forget to give your valuable comments for the article or you can send me the direct queries to my email id askvivekjohari@gmail.com. If you want, you can also join our Facebook community here or follow me on Twitter.

The post Point In Time Restore for Azure SQL Database appeared first on Technology with Vivek Johari.