6

I'm working on a task where I need to backup and restore a database in another instance.

But, when I restore that database, I need to Drop all the History Tables associated with Temporal Tables.

Is there a quick way to do this? Any help is appreciated.

6

I believe that's what you need.

SET NOCOUNT ON ;
DECLARE @cmd NVARCHAR (MAX);

DECLARE
    @SchemaName VARCHAR(100),
    @TableName VARCHAR(100),
    @HistorySchemaName VARCHAR(100),
    @HistoryTableName VARCHAR(100);

SET @cmd = '' ;

SELECT TOP ( 1 )
       @SchemaName = SCHEMA_NAME(T1.schema_id ),
       @TableName = T1 .name,
       @HistorySchemaName = SCHEMA_NAME(T2.schema_id ),
       @HistoryTableName = T2 .name
FROM sys .tables T1
LEFT JOIN sys .tables T2
    ON T1.history_table_id = T2 .object_id
WHERE T1. temporal_type = 2
ORDER BY T1. name;


WHILE @@ROWCOUNT = 1
    BEGIN
        SET @cmd = 'ALTER TABLE ' + QUOTENAME (@SchemaName ) + '. ' + QUOTENAME(@TableName) + ' SET ( SYSTEM_VERSIONING = OFF );
DROP TABLE '       + QUOTENAME (@HistorySchemaName ) + '.' + QUOTENAME(@HistoryTableName );
        EXEC sp_executesql @cmd

        SELECT TOP ( 1 )
               @SchemaName = SCHEMA_NAME(T1.schema_id ),
               @TableName = T1 .name,
               @HistorySchemaName = SCHEMA_NAME(T2.schema_id ),
               @HistoryTableName = T2 .name
        FROM sys.tables T1
        LEFT JOIN sys.tables T2
            ON T1 .history_table_id = T2 .object_id
        WHERE
            T1 .temporal_type = 2
            AND T1 .name > @TableName
        ORDER BY T1 .name;
    END;
  • 1
    Probably one of the fastest answers I've ever come across! You obviously must have had this script in your back pocket. Maybe worth providing some context just for others as they come across it. – John Eisbrener Mar 20 at 15:43
  • @bege1, did that worked for you? – Dan Mar 20 at 15:55
  • @John, yes indeed! I do have this script but wrapped inside of a stored procedure where I used to need to remove history data when moving database around in a environment with multi-tenant databases. – Dan Mar 20 at 15:55
  • This is working perfectly!!!!! Thanks for sharing @Dan – bege1 Mar 21 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.