SQL server on EC2 restores

Clumio lets you recover a Microsoft SQL database from the backups that have been created and stored in the cloud. You can restore a database as a new database, overwrite the source database, restore the database to a specific point in time for log backups, or restore a secondary database of an Availability Group.

The recovery options available to you depend on the recovery model of your Microsoft SQL database. Full recovery contains logs which allow for point in time recovery, simple recovery which allows for database recovery, or bulk logged recovery which has limited log records.

The backup history calendar displays the dates with available backups. A legend below the calendar indicates the type of backups available for recovery. Click the date corresponding to the backup you want to restore. A popup dialog box displays with a list of backups generated on that day.

Each database backup includes an expiry date after which it is no longer available to be restored. The expiry date is based on the retention schedule of the policy covering the backup. Point in Time recovery and Bulk Logged recovery do not show an expiry date.

Space requirements to restore a database

The following table shows the space requirements to restore Microsoft SQL databases in Clumio. The term "space" in the table refers to the current space used by the database to be recovered.

Recover MS SQL database as a new database

1. Navigate to Restore > AWS > MS SQL on EC2 and type in the name of the database you want to recover or browse your inventory to select a database to restore.
2. On the calendar, click the date that contains a backup you want to recover.
3. In the dialog that displays click Restore from the Database option.
4. The Restore as new database option is selected by default.
5. The new database name will have 'restored' as a prefix to the original name by default. If you want to change the name, it cannot exceed 128 characters and cannot contain quotation marks.
6. Select an account to restore the backup to and then select a host and instance. The default selections are on the same account, host, and instance that the backup was taken from.
7. The Restore the database in "Restoring..." mode option restores the database to a non-operational state. This state is useful for various purposes, such as performing maintenance tasks, troubleshooting issues, adding to an availability group or preparing for a subsequent restoration to an operational state.
8. Click Next.
9. The Recover the files to different location option is selected by default. Edit the fields to customize the file paths but you must ensure that the directories exist on the target. If the directories do not exist, or there are filename conflicts, the recovery will fail.
10. Click Restore to start the recovery process.

Recover MS SQL database to a point in time

Point in time recovery is only available for log backups.

1. Navigate to Restore > AWS > MS SQL on EC2 and type in the name of the database you want to recover or browse your inventory to select a database to restore.
2. On the calendar, click the date that contains a backup you want to recover.
3. In the dialog that displays, click Restore to a point in time.
4. Pick a time to which to restore the database. If any logs are missing a warning displays letting you know that the recovery window is limited as a result.
5. The Restore as new database option is selected by default.
6. The new database name will have 'restored' as a prefix to the original name by default. If you want to change the name, it cannot exceed 128 characters and cannot contain quotation marks.
7. Select an account to restore the backup to and then select a host and instance. The default selections are on the same account, host, and instance that the backup was taken from.
8. The Restore the database in "Restoring..." mode option restores the database to a non-operational state. This state is useful for various purposes, such as performing maintenance tasks, troubleshooting issues, adding to an availability group or preparing for a subsequent restoration to an operational state.
9. Click Next.
10. The Recover the files to different location option is selected by default. Edit the fields to customize the file paths but you must ensure that the directories exist on the target. If the directories do not exist, or there are filename conflicts, the recovery will fail.
11. Click Restore to start the recovery process.

Recover secondary database of an Availability Group

This option lets you restore a database to the same Availability Group as a secondary database on a secondary replica. This is useful in the following scenarios:

• An existing secondary database becomes unsynchronized or disconnected from the Availability Group.
• A failover of primary has occurred and as a result, an Availability Group database on the old primary has become unsynchronized and needs recovery.
• An Availability Group database needs to be added to a secondary replica as a new database.

Prerequisites

• All replicas of Availability Group must be in the same account and region.
• The selected SQL instance must belong to a connected secondary replica of the Availability Group.
• The Availability Group must have a healthy current primary replica on a different SQL instance.
• The database to restore on the secondary replica must have a corresponding synchronized primary database on the current primary replica.
• Enough space must exist on the disk for the restored file:
  • As the secondary database is being restored to another existing database, the space needed is the restored file size.
• If overwriting an existing secondary database that’s part of an Availability Group, the existing database must first be removed from the Availability Group.

Note: Log backups for the selected database will be briefly paused during the restore process. All data will be backed up when log backups resume.

1. Navigate to Restore > AWS > MS SQL on EC2 and type in the name of the database you want to recover or browse your inventory to select a database to restore.
2. On the calendar, click the date that contains a backup you want to recover.
3. In the dialog that displays, click Restore as a secondary AAG database.
4. Select a host and an instance to restore the database. The instance you select must already be added to the Availability Group, otherwise the restore operation will fail.
5. Click Next.
6. The Recover the files to original location option is selected by default. If you select the Recover the files to different location option, you must ensure that the directories exist on the target. If the directories do not exist, or there are filename conflicts, the recovery will fail.
7. Click Restore.

Sample script to automate restoration of a secondary database in an AAG

The following script is provided as a sample script for reference and you can use a similar script as per your requirements and conditions. Refer to the Clumio API Reference for base API URLs.

import requests

# Update these 5 fields. See API Reference for Base API URLs.
# NOTE that all names below are assumed to be unique.
base_url = '<clumio-base-api-url>'
token = '<clumio-api-token>'
source_database_name = '<source-database-name>'
target_secondary_host_name = '<target-host-name>'
target_sql_instance_name = '<target-instance-name>'

# REST API header
headers = {'Authorization': f'Bearer {token}'}

# GET source database ID
params = '{"name": {"$contains": "%s"}}' % source_database_name
url = f'{base_url}/api/datasources/aws/ec2-mssql/databases?filter={params}'
databases = requests.get(url, headers=headers).json()['_embedded']['items']
database_id = databases[0]['id']

# GET target host ID
params = '{"name": {"$contains": "%s"}}' % target_secondary_host_name
url = f'{base_url}/api/datasources/aws/ec2-mssql/hosts?filter={params}'
hosts = requests.get(url, headers=headers).json()['_embedded']['items']
host_id = hosts[0]['id']

# GET target SQL instance ID
params = (
    '{"host_id": {"$eq": "%s"}, "name": {"$contains": "%s"}}' % (host_id, target_sql_instance_name)
)
url = f'{base_url}/api/datasources/aws/ec2-mssql/instances?filter={params}'
instances = requests.get(url, headers=headers).json()['_embedded']['items']
target_sql_instance_id = instances[0]['id']

# POST restore to secondary AAG (DB must NOT be in a synchronizing state)
params = {
    "source": {"restore_to_aag": {"database_id": database_id}},
    "target": {
        "database_name": source_database_name,
        "instance_id": target_sql_instance_id,
        "should_keep_source_config": True}
}
url = f'{base_url}/api/restores/aws/ec2-mssql/databases'

response = requests.post(url, headers=headers, json=params).json()
print('Restore task ID: %s' % response['task_id'])