Perform a database restore by using database tools

Point-in-time restore is a self-service option that reinstates a database to a specific point in time. Typically, Azure SQL Database keeps a record of all changes made in the database for the last 7-35 days, which allows you to restore the database at a given point within this time frame.

To perform a point-in-time restore, use the Azure portal.

First, navigate to your SQL Database and select “Restore”. Your backup is automatically named, you just need to choose the restore point:

mysql
Restore Point: <Select the date and time you want to restore up to>

2. Geo-Restore

Geo-Restore provides you with an option to restore the database in a region different from that of the original database. This option is quite beneficial in scenarios of a regional disaster.

For a Geo-Restore, use PowerShell. Remember the target server must exist before starting the restore, and it might take longer compared to Point-in-time restore.

azurepowershell
New-AzSqlDatabaseRestore -FromGeoBackup `
-ResourceGroupName “myResourceGroup” `
-ServerName $targetServerName `
-TargetDatabaseName “MySampleDatabase” `
-RecoveryServicesRecoveryPointResourceId $rp.Id `
-Edition “Standard” `
-ServiceObjectiveName “S2”

3. Database Copy

Another method of restoring the database would be using the copy operation. The copy database operation creates a new database that is a full-size copy of an existing database. The new database is created on the same server as the original database and it can have a different name.

In Azure portal, create a database copy using the following command:

sql
CREATE DATABASE destination_database AS COPY OF [source_server_name].source_database;

Note: Replace ‘destination_database’ with the name you want for your new database and ‘source_database’ with the name of the existing database.

These three mechanisms provide powerful ways to restore databases on Microsoft Azure SQL. As for which method to use, it comes down to your requirements.

Each method has its strengths and potential drawbacks. Point-in-time restores are quick and offer precise restore points, but they’re not suitable for geographically diverse recovery. Geo-Restores offer that geographical diversity but with a slower recovery speed. Finally, Copy Database offers a compromise with fast recovery times, a near real-time state, but a moderate operational impact. Learn to leverage these recovery strategies and enhance your Azure SQL Database administration skills.

Practice Test

True or False: In Microsoft Azure SQL, a database restore operation can be undone if deemed unnecessary.

• True
• False

Answer: False.

Explanation: Once a restore operation has been started in Microsoft Azure SQL, it cannot be undone, the process is permanent.

Which of the following methods can be used to perform a database restore in Azure?

• a. Azure portal
• b. T-SQL
• c. PowerShell
• d. All of the above

Answer: d. All of the above.

Explanation: All of these tools can be used to perform a database restore operation in Azure. The method to be used will depend on the specific requirements of the situation.

True or False: You can restore a deleted Azure SQL database using Azure portal.

• True

• False

Answer: True.

Explanation: Yes, a deleted Azure SQL database can be restored from the Azure portal as long as it falls within the retention period.

Which tool is NOT suitable for using to restore a database in Azure SQL?

• a. SSMS
• b. VS Code
• c. Azure CLI
• d. Python

Answer: d. Python

Explanation: While Python is a powerful language useful for many tasks, it is not typically used for restoring databases in Azure SQL.

Multiple choice: You can restore your Azure SQL database to a ________.

• a. Different server
• b. Point in time within the last 30 days
• c. Either a or b
• d. Neither a nor b

Answer: c. Either a or b

Explanation: You can restore your Azure SQL database to a different server or a point in time within the last 30 days based on configuration settings.

True or False: Azure SQL database does not support long-term backup retention.

• True
• False

Answer: False

Explanation: Azure SQL database does support long-term backup retention, but configuration for this feature is required.

In Azure SQL, each automatic backup can be retained for a maximum of how many days?

• a. 7 days
• b. 14 days
• c. 35 days
• d. 60 days

Answer: c. 35 days

Explanation: For Azure SQL, each automatic backup can be retained for a maximum of 35 days.

True or False: Restoration time of a database in Azure SQL is dependent on the database size.

• True
• False

Answer: True.

Explanation: The restoration time in Azure SQL is highly dependent on the size of the database. Larger databases take more time to restore.

To recognize corruption or deletion in your database, what Azure SQL solution allows you to restore it to a point before these occurred?

• a. Long term retention
• b. Automatic backup
• c. Point-in-time restore
• d. None of the above

Answer: c. Point-in-time restore

Explanation: Point-in-time restore allows you to restore your database to a state before corruption or deletion occurred.

True or False: Azure SQL only allows restoration of databases that are still up and running.

• True
• False

Answer: False.

Explanation: Azure SQL supports the restoration of databases even after they have been deleted, as long as it is within the retention period.

Interview Questions

What is the basic command syntax for restoring a database in Azure SQL Database?

The basic syntax for restoring a database in Azure is RESTORE DATABASE database_name FROM URL = ‘backup_location’ WITH CREDENTIAL = ‘credential_name’.

Which tool can you use to perform an Azure SQL database restore task?

You can use either the Azure portal, Azure PowerShell, or Azure CLI to perform an Azure SQL database restore task.

What additional information is needed when restoring database from Geo-Replicated backups in Azure SQL database?

When restoring a database from Geo-Replicated backups, you will need to specify the recovered database’s name and the point in time to which the database should be restored.

What is the primary purpose of Azure SQL’s Point-in-time restore feature?

Point-in-Time restore feature allows you to restore a database from a backup to any point within the last 35 days.

How can you automate database restoration in Azure SQL Database?

You can automate database restoration by using Azure Automation, which allows you to schedule and automate the process.

Is it possible to restore a deleted database in Azure SQL Database?

Yes, in Azure SQL Database, you can restore a deleted database within its retention period.

Which command can be used to restore a database in Azure SQL Managed Instance?

The RESTORE DATABASE command can be used to restore a database in Azure SQL Managed Instance.

How to secure database backups in Azure SQL Database?

All backups in Azure SQL Database are automatically encrypted using Azure Storage Service Encryption.

Why is it important to perform a “dry run” restoration to validate the backup files within Azure SQL Database?

Performing a “dry run” restoration can help identify any potential issues in the backup files that could prevent successful restoration.

How long does Azure SQL Database keep the automated backups?

Azure SQL Database keeps automated full backups for seven days, differential backups for 12 hours, and transaction log backups for 5-10 minutes.

What is the purpose of using Azure’s Long-Term Backup Retention?

Azure’s Long-Term Backup Retention helps to meet the compliance needs, allowing to store the backups for up to 10 years.

In a managed instance, can you restore a larger database to a smaller one?

No, you cannot restore a larger database to a smaller one in a Managed Instance. The size of the target must be equal or larger than the source.

How can you ensure High Availability during the restoration process in Azure SQL Database?

Use Active Geo-Replication, which creates readable secondary databases in the same or different data center. If the primary database fails, failover can be initiated to any secondary.

Can you restore a database backup taken from SQL Server to Azure SQL Database?

No, direct restoration of a backup from SQL Server to Azure SQL Database is not supported. You need to use tools like Database Migration Service or the Deploy Database to Microsoft Azure SQL Database wizard in SQL Server Management Studio.

What is deleted database backup in Azure SQL Database?

When a database is deleted, a final backup is also taken and retained for 7 days. This final backup is referred to as deleted database backup.