Skip to content

Latest commit

 

History

History
79 lines (56 loc) · 3.74 KB

disable-stretch-database-and-bring-back-remote-data.md

File metadata and controls

79 lines (56 loc) · 3.74 KB
Raw
title description author ms.author ms.reviewer ms.date ms.service ms.topic helpviewer_keywords
Disable Stretch Database and bring back remote data
Disable Stretch Database and bring back remote data
MikeRayMSFT
mikeray
randolphwest
07/25/2022
sql-server-stretch-database
conceptual
Stretch Database, disabling
disabling Stretch Database

Disable Stretch Database and bring back remote data

[!INCLUDE sqlserver2016-windows-only]

Important

[!INCLUDE stretch-database-deprecation]

You can use Transact-SQL to disable Stretch Database for a table or for a database.

After you disable Stretch Database for a table, data migration stops and query results no longer include results from the remote table.

If you want to pause data migration, see Pause and resume data migration (Stretch Database).

Note

Disabling Stretch Database for a table or for a database does not delete the remote object. If you want to delete the remote table or the remote database, you have to drop it by using the Azure management portal. The remote objects continue to incur Azure costs until you delete them. For more info, see SQL Server Stretch Database Pricing.

Disable Stretch Database for a table

Use Transact-SQL

  • To disable Stretch for a table and copy the remote data for the table from Azure back to SQL Server, run the following command. After all the remote data has been copied from Azure back to SQL Server, Stretch is disabled for the table.

    This command can't be canceled.

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

    Copying the remote data for the table from Azure back to SQL Server incurs data transfer costs. For more info, see Data Transfers Pricing Details.

  • To disable Stretch for a table and abandon the remote data, run the following command.

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

    Disabling Stretch Database for a table does not delete the remote data or the remote table. If you want to delete the remote table, you have to drop it by using the Azure management portal. The remote table continues to incur Azure costs until you delete it. For more info, see SQL Server Stretch Database Pricing.

Disable Stretch Database for a database

Before you can disable Stretch Database for a database, you have to disable Stretch Database on the individual Stretch-enabled tables in the database.

Use Transact-SQL

Run the following command.

ALTER DATABASE [<Stretch-enabled database name>]
    SET REMOTE_DATA_ARCHIVE = OFF;
GO

Disabling Stretch Database for a database doesn't delete the remote database. If you want to delete the remote database, you have to drop it by using the Azure management portal. The remote database continues to incur Azure costs until you delete it. For more info, see SQL Server Stretch Database Pricing.

See also