Pause and resume data migration (Stretch Database)

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

Important

Stretch Database is deprecated in SQL Server 2022 (16.x) and Azure SQL Database. This feature will be removed in a future version of the Database Engine. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

To pause or resume data migration to Azure, select Stretch for a table in SQL Server Management Studio, and then select Pause to pause data migration or Resume to resume data migration. You can also use Transact-SQL to pause or resume data migration.

Pause data migration on individual tables when you want to troubleshoot problems on the local server or to maximize the available network bandwidth.

Pause data migration

Use SQL Server Management Studio

  1. In SQL Server Management Studio, in Object Explorer, select the Stretch-enabled table for which you want to pause data migration.

  2. Right-click and select Stretch > Pause.

Use Transact-SQL

Run the following command.

USE [<Stretch-enabled database name>];
GO
ALTER TABLE [<Stretch-enabled table name>]
    SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = PAUSED ) );
GO

Resume data migration

Use SQL Server Management Studio

  1. In SQL Server Management Studio, in Object Explorer, select the Stretch-enabled table for which you want to resume data migration.

  2. Right-click and select Stretch > Resume.

Use Transact-SQL

Run the following command.

USE [<Stretch-enabled database name>];
GO
ALTER TABLE [<Stretch-enabled table name>]
    SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = OUTBOUND ) );
GO

Check whether migration is active or paused

Use SQL Server Management Studio

In SQL Server Management Studio, open Stretch Database Monitor and check the value of the Migration State column. For more info, see Monitor and troubleshoot data migration.

Use Transact-SQL

Query the catalog view sys.remote_data_archive_tables and check the value of the is_migration_paused column. For more info, see sys.remote_data_archive_tables.

See also