Exercise - Recover data by restoring an Azure SQL database

  • 7 minutes

Trial restores are a key component of any disaster recovery strategy.

You want to familiarize yourself with the steps to restore a backed-up database to a specific point in time, in case it becomes necessary. You also want to investigate how long a restore operation takes, a key part of the calculation of Recovery Time Objection (RTO). You can plan for this time in your guidance for your organization.

You can treat the restored database as a replacement for the original database or use it as a data source to update the original database. Though you can overwrite and replace a database in a SQL Server instance or Azure SQL Managed Instance, you cannot overwrite an Azure SQL Database with a restore.

Let's perform a restore from automated Azure SQL Database backups.

Confirm that backups are active

Tip

It can take up to 15 minutes after database creation for the first successful backup to finish.

Let's look at the backups Azure SQL takes for us, automatically.

  1. In Azure Cloud Shell, run the following PowerShell command to set a variable to the value of your SQL Server instance:

    $sqlserver=Get-AzSqlServer

  2. View the available restore points, based on backups, with the Get-AzSqlDatabaseRestorePoint PowerShell cmdlet:

    Get-AzSqlDatabaseRestorePoint `
    -ResourceGroupName <rgn>[sandbox resource group name]</rgn> `
    -DatabaseName sql-erp-db `
    -ServerName $sqlserver.ServerName

    You should get an output similar to the following code. If the command returns no value, a backup hasn't started yet. Rerun this command in a couple of minutes.

    ResourceGroupName        : <rgn>[sandbox resource group name]</rgn>
ServerName               : erpserver-53903
DatabaseName             : sql-erp-db
Location                 : East US
RestorePointType         : CONTINUOUS
RestorePointCreationDate :
EarliestRestoreDate      : 9/24/19 4:21:21 PM
RestorePointLabel        :

    RestorePointType is CONTINUOUS, indicating that backups are automatically happening. EarliestRestoreDate indicates the timestamp of the first backup. With backups in place, let's continue the exercise.

Drop a table from the database

Let's start by simulating a mistaken database modification.

  1. On the Azure portal menu or from the Home page, select All resources, select erpserver-NNNN, select SQL databases, and then select the sql-erp-db database.

  2. We'll use the T-SQL query editor built into the Azure portal. Select Query editor (preview), then sign in with the dbadmin user and the password that you specified for this account. The following T-SQL commands would also work in SQL Server Management Studio, or the mssql extension for Visual Studio Code, or other T-SQL query tools.

  3. Let's drop the Person table that we created earlier. In a new query window, run this command. Note the time on your clock.

    DROP TABLE Person

  4. To check the tables in the database, select New Query. Then, in the Query 2 window, run this command to list all tables in the database:

    SELECT schema_name(t.schema_id) as schema_name,
    t.name as table_name
FROM sys.tables AS t
ORDER BY schema_name, table_name;

    You should see No results returned, because we deleted the Person table.

    Screenshot that shows no results returned after querying for the tables in the database.

Create a point-in-time restore

The Person table was mistakenly deleted. Now, let's restore the database to its previous state.

  1. On the Azure portal menu or from the Home page, select All resources, and then select the sql-erp-db database.

  2. At the top of the Overview page, select Restore.

  3. Complete the Basics tab on the Restore database page with these values, and then select Review + create. Here, you provide a new database name for the restored version of the database.

    Setting Value
    Select source Point-in-time
    Database name sql-erp-db-restored
    Restore point Select a time before you dropped the Person table, perhaps 10 minutes ago.
    Server erpserver-xxxxx
    Want to use SQL elastic pool? No
    Compute + storage Default value
    Backup storage redundancy Locally-redundant backup storage

    Screenshot that shows the restore database page with the Review + create button selected.

  4. Select Create. The database restore takes several minutes to complete.

View the restored database

The restored database contains the Person table.

  1. In the Azure portal menu or from the Home page, select All resources, and then select the sql-erp-db-restored database.

  2. Select Query editor (preview), and then sign in with the dbadmin user and the password that you specified for this account.

  3. To check the tables in the database, in the Query 1 window, run this command:

    SELECT schema_name(t.schema_id) as schema_name,
    t.name as table_name
FROM sys.tables AS t
ORDER BY schema_name, table_name;

    The database has been restore to the state it was, including the Person table.

    Screenshot showing results after querying for the tables in the database.

  4. Confirm that the data is in the table by running this command:

    SELECT * FROM Person;

    You should see the data that you entered previously.

    Screenshot showing confirmed results after querying for the tables in the database.

You've now learned how you can restore a database if something unintended happens to the data. You've familiarized yourself with the restore process. You can now assure your organization that you've properly defined the backup and restore procedures.