Restore Stretch-enabled databases (Stretch Database)

Applies to: SQL Server 2016 (13.x) and later - Windows only

Restore a backed up database when necessary to recover from many types of failures, errors, and disasters.

For more info about backup, see Backup Stretch-enabled databases.

Backing up is only one part of a complete high availability and business continuity solution. For more info about high availability, see High Availability Solutions.

Restore your SQL Server data

To recover from hardware failure or corruption, restore the Stretch-enabled SQL Server database from a backup. You can continue to use the SQL Server restore methods that you currently use. For more info, see Restore and Recovery Overview.

After you restore the SQL Server database, you have to run the stored procedure sys.sp_rda_reauthorize_db to re-establish the connection between the Stretch-enabled SQL Server database and the remote Azure database. For more info, see Restore the connection between the SQL Server database and the remote Azure database.

Restore your remote Azure data

Recover a live Azure database

The SQL Server Stretch Database service on Azure snapshots all live data at least every 8 hours using Azure Storage Snapshots. These snapshots are maintained for seven days. This allows you to restore the data to one of at least 21 points in time within the past seven days up to the time when the last snapshot was taken.

To restore a live Azure database to an earlier point in time by using the Azure portal, do the following things.

1. Sign in to the Azure portal.
2. On the left side of the screen select Browse and then select SQL Databases.
3. Navigate to your database and select it.
4. At the top of the database pane, select Restore.
5. Specify a new Database name, select a Restore Point and then select Create.
6. The database restore process will begin and can be monitored using Notifications.

Recover a deleted Azure database

The SQL Server Stretch Database service on Azure takes a database snapshot before a database is dropped and retains it for seven days. After this occurs, it no longer retains snapshots from the live database. This lets you restore a deleted database to the point when it was deleted.

To restore a deleted Azure database to the point when it was deleted by using the Azure portal, do the following things.

1. Sign in to the Azure portal.
2. On the left side of the screen select Browse and then select SQL Servers.
3. Navigate to your server and select it.
4. Scroll down to Operations on your server's pane, and select the Deleted Databases tile.
5. Select the deleted database you want to restore.
6. Specify a new Database name and select Create.
7. The database restore process will begin and can be monitored using Notifications.

Restore the connection between the SQL Server database and the remote Azure database

1. If you're going to connect to a restored Azure database with a different name, or in a different region, run the stored procedure sys.sp_rda_deauthorize_db to disconnect from the previous Azure database.

2. Run the stored procedure sys.sp_rda_reauthorize_db to reconnect the local Stretch-enabled database to the Azure database.

   • Provide the existing database scoped credential as a sysname or a varchar(128) value. (Don't use varchar(max).) You can look up the credential name in the view sys.database_scoped_credentials.

   • Specify whether to make a copy of the remote data and connect to the copy (recommended).

     USE <Stretch-enabled database name>;
     GO
     EXEC sp_rda_reauthorize_db
         @credential = N'<existing_database_scoped_credential_name>',
         @with_copy = 1;
     GO
     

See also

• Backup Stretch-enabled databases
• Manage and troubleshoot Stretch Database
• sys.sp_rda_reauthorize_db
• sys.sp_rda_deauthorize_db
• Back Up and Restore of SQL Server Databases