Point-in-Time Restore is a feature of Azure SQL Database that allows you to restore a database to an earlier point in time, down to the second. This feature provides a safety net in your data protection strategy, allowing recovery from accidental deletion or modification of data within the automatic backup retention period – up to 35 days in Basic, Standard, and Premium service tiers.
For instance, if some data was accidentally deleted or data became corrupted, a restore operation can be initiated to a point in time before the deletion or corruption occurred, therefore providing seamless data recovery.
Steps to Perform Point In Time Restore
- Login to Azure Portal: Open the Azure Portal (portal.azure.com) and log in using your Azure account.
- Navigate to SQL databases: From the left-hand sidebar, click on SQL databases.
- Select the Database: Click on the database you want to restore.
- Restore the Database: Click on the restore option located at the top of the window. A new panel will open to the right.
- Select Point in Time: From the dropdown, select the ‘Point in Time’ option.
- Choose the Restore point: Use the slider or enter the exact date and time manually.
- Name Your Database: Enter a new name for the restored database.
- Click on OK: After specifying all information, click on the OK button. The restoration process may take a while depending on the size of your database and the chosen restore point.
Command Line Way
Performing point-in-time restore can also be done via Azure CLI or PowerShell.
The sample Azure CLI code is provided as follows:
az sql db restore --dest-name MyDatabases --name MyDatabase --resource-group MyResourceGroup --server myserver --time "2018-05-20T05:34:22Z" --no-wait
In this brought example:
- MyDatabases is a name of your restoring database
- MyDatabase is the name of the original database
- MyResourceGroup is the name of your resource group
- myserver is a name of your server
- The –time flag indicates the point to which you wish to restore your database.
Similarly, for PowerShell, the cmdlet would be:
Restore-AzSqlDatabase -FromPointInTimeBackup -PointInTime "2018-05-20 5:34:22 AM" -ResourceGroupName "MyResourceGroup" -ServerName "myserver" -TargetDatabaseName "MyDatabases" -Id "/subscriptions/..."
Such features of Azure SQL like Point-in-Time Restore elevate the importance of the platform as a robust and reliable database management solution. It assures that in any situation of data loss or corruption, a copy of your database at a particular point in time is always available to be restored as per your requirements. Regardless of whether you are preparing for DP-300 Administering Microsoft Azure SQL Solutions examination or not, understanding this operation is vital in the realm of database administration.
Practice Test
True or False: Microsoft Azure SQL Solutions allow a Point-in-Time Restore (PITR) for restoring the database to a specific moment.
• True
• False
Answer: True
Explanation: Azure SQL Database automatically creates database backups that are kept between 7 and 35 days, allowing PITR in that timeframe.
True or False: Azure SQL Database backups are preserved for a maximum of 7 days.
• True
• False
Answer: False
Explanation: Azure SQL Database backups are kept for a period between 7 and 35 days, allowing a Point-in-Time Restore within that timeframe.
What is the primary purpose of Point-in-Time Restore (PITR) in Azure SQL?
• To back up data.
• To restore the database to a specific moment.
• To delete the database.
Answer: b) To restore the database to a specific moment.
Explanation: PITR is a powerful feature in Azure SQL that allows the user to recover the database to a specific time.
True or False: In Azure SQL Solutions, a new database is created during a Point-in-Time Restore and the existing database is not replaced.
• True
• False
Answer: True
Explanation: When performing Point-in-Time Restore in Azure SQL, a new database is created with the state of the original database at the specific point in time.
In Azure SQL Database, what is the duration for which backups are stored with business critical and premium service tiers?
• 7 days
• 14 days
• 35 days
• 42 days
Answer: c) 35 days
Explanation: For databases using the business critical and premium service tiers, the backups are stored for a duration of 35 days.
True False: In Azure SQL, it is not possible to perform a Point-in-Time Restore after a database is deleted.
• True
• False
Answer: False
Explanation: After deleting a database, a user has 7 days to restore it through backup using Point-in-Time Restore.
What are the necessary conditions for a Point-in-Time Restore (PITR) in Azure SQL Database? Select all that apply.
• The database is online.
• The restore point is within the last backup retention period.
• The database is deleted.
• Existing database is replaced with the new database during the restore.
Answer: a) The database is online. b) The restore point is within the last backup retention period.
Explanation: For a Point-in-Time Restore, the database must be online and the restore point must be within the last backup retention period.
True or False: You cannot control when backup operations occur in Azure SQL databases.
• True
• False
Answer: True
Explanation: Backup operations for Azure SQL databases are automated and managed internally by Azure. Users have no control over when these operations occur.
What is the catastrophic-level disaster recovery feature in Azure that provides an additional level of data protection and allows database recovery far beyond the point of the available backups?
• PITR
• Active Geo-Replication
• Failover Group
• Geo Restore
Answer: d) Geo Restore
Explanation: Geo Restore is a catastrophic-level disaster recovery feature that provides the ability to restore a database from a geo-replicated backup.
True or False: The Long-term backup retention (LTR) feature allows you to restore Point-in-Time Restore for up to 10 years.
• True
• False
Answer: True
Explanation: The Long-term backup retention (LTR) feature in Azure SQL Database enables you to automatically retain database backups in Azure Blob storage for up to 10 years.
Interview Questions
What is a Point-in-Time Restore in Azure SQL Database?
Point-in-time restore is a self-service capability, which allows you to restore a Basic, Standard, or Premium database from a backup to any point within the retention period.
What is the default backup retention period for an Azure SQL Database?
The default backup retention period for an Azure SQL Database is seven days.
When performing a Point-in-Time Restore, do you need to specify the restore destination?
Yes, you need to specify a new database name as the restore destination. It does not overwrite the existing database.
Can you use Point-in-Time Restore to recover dropped databases?
No, Point-in-Time Restore is a feature that can restore a database to its state at a specific point in time but it cannot recover a dropped database.
How can you perform point-in-time restore in Azure SQL Database?
Point-in-Time Restore can be performed using the Azure portal, Azure CLI, PowerShell, or REST API.
What kind of permissions do you need to perform a point-in-time restore?
To perform a point-in-time restore, you need to be the owner of the server or a member of the Azure SQL Database Contributor role.
Can you restore a database to a point in time before its creation in Azure SQL?
No, you cannot restore a database to a point in time before its creation.
What happens to the transaction logs after an Azure SQL Database point-in-time restore operation?
During a point-in-time restore operation, transaction logs are replayed to bring the database up to the specified point in time.
Can you change the backup retention period in Azure SQL Database?
Yes, you can change the retention period up to 35 days in Azure SQL Database.
Which types of Azure SQL Database support point-in-time restore?
Basic, Standard, and Premium databases support point-in-time restore.
What is the significance of the Last Available Backup Date field when performing a Point-In-Time Restore?
The Last Available Backup Date field informs you how recent your most recent backup is, and thus how recent a point in time you can restore to.
Are backups of Azure SQL Databases automatically taken and managed by Azure?
Yes, Azure automatically takes backups of SQL Databases and manages these backups.
Can you use the Azure portal to perform a Point-In-Time Restore?
Yes, you can use the Azure portal to perform a Point-In-Time restore.
Is it possible to restore a deleted Azure SQL Database?
Yes, Azure SQL Database provides a backup-based system for restoring deleted databases, known as a “geo-restore”.
Is the original database replaced or affected when performing a Point-In-Time Restore?
No, the original database is not replaced or affected. The restore operation creates a new database from the selected point in time.
extutorials.com