Maklum balas Edit

Disable Stretch Database and bring back remote data

  • Artikel
  • 4 minit untuk dibaca

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

To disable Stretch Database for a table, select Stretch for a table in SQL Server Management Studio. Then select one of the following options.

  • Disable | Bring data back from Azure. Copy the remote data for the table from Azure back to SQL Server, then disable Stretch Database for the table. This operation incurs data transfer costs, and it can't be canceled.

  • Disable | Leave data in Azure. Disable Stretch Database for the table. Abandon the remote data for the table in Azure.

You can also 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 simply 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 SQL Server Management Studio to disable Stretch Database for a table

  1. In SQL Server Management Studio, in Object Explorer, select the table for which you want to disable Stretch Database.

  2. Right-click and select Stretch, and then select one of the following options.

    • Disable | Bring data back from Azure. Copy the remote data for the table from Azure back to SQL Server, then disable Stretch Database for the table. This command can't be canceled.

      Note

      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.

      After all the remote data has been copied from Azure back to SQL Server, Stretch is disabled for the table.

    • Disable | Leave data in Azure. Disable Stretch Database for the table. Abandon the remote data for the table in Azure.

    Note

    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.

Use Transact-SQL to disable Stretch Database for a table

  • 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

    Note

    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

Note

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 SQL Server Management Studio to disable Stretch Database for a database

  1. In SQL Server Management Studio, in Object Explorer, select the database for which you want to disable Stretch Database.

  2. Right-click and select Tasks, and then select Stretch, and then select Disable.

Note

Disabling Stretch Database for a database does not 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.

Use Transact-SQL to disable Stretch Database for a database

Run the following command.

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

Note

Disabling Stretch Database for a database does not 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

ALTER DATABASE SET Options (Transact-SQL)
Pause and resume data migration (Stretch Database)