Pause and resume data migration (Stretch Database)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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 to pause data migration

  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, and then select Pause.

Use Transact-SQL to pause data migration

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 to resume data migration

  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, and then select Resume.

Use Transact-SQL to resume data migration

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 to check whether migration is active or paused

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 to check whether migration is active or paused

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

ALTER TABLE (Transact-SQL)
Monitor and troubleshoot data migration