Restoring All Triggers and Foreign Keys

• Restore all table-level triggers.

  DECLARE @sql NVARCHAR(MAX) = '';
  SELECT @sql = @sql + 'ENABLE TRIGGER ' + QUOTENAME(name) + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_id)) + ';' + CHAR(13)
  FROM sys.triggers
  WHERE is_ms_shipped = 0 -- Exclude system triggers.
  AND parent_class_desc = 'OBJECT_OR_COLUMN'; -- Only table-level triggers are included.
  EXEC sp_executesql @sql;

• Restore all foreign keys.

  DECLARE @sql NVARCHAR(MAX) = '';
  SELECT @sql = @sql + 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) + ' CHECK CONSTRAINT ' + QUOTENAME(name) + ';' + CHAR(13)
  FROM sys.foreign_keys;
  EXEC sp_executesql @sql;

  

ENABLE TRIGGER ALL ON DATABASE;

• Restore the source data service to the original edition.

  ALTER DATABASE smsa_mig
  MODIFY (EDITION = 'Standard', SERVICE_OBJECTIVE = 'S0'); -- Set EDITION and SERVICE_OBJECTIVE based on the queried original database edition.

  You can run the SQL script to check whether the source service edition is restored (which may take some time. If not, restore it again).

  

  • This step is required for Azure SQL Database editions that do not support CDC.
  • The following editions do not support CDC: Basic, Standard S0, Standard S1, and Standard S2.
  • The billing varies depending on the edition of Azure SQL Database.
• Query the original service edition of the database.

  SELECT 
      database_name = DB_NAME(),
      edition,
      service_objective
  FROM sys.database_service_objectives;