The hurdles and pitfalls of moving or migrating a System-versioned temporal table cross database

Maybe you already have your own way of doing this and are wondering about alternative methods, or maybe you are planning to implement them and are asking yourself this question. Or maybe your colleague introduced this to the developers team and suddenly they are popping up all around your databases like mushrooms and you need a quick guide about how to move or migrate system versioned tables from one to another database. This was a rather specific use case for me, and I thought it would be a nice idea to share.

TO BEGIN:

What is a temporal table, also called a temporal system-versioned table ?

Temporal tables are part of the Microsoft SQL Server tool kit since the 2016 version. It enables you to track data changes with the use of a separate history table linked with the original table. This will provide you with the full history of any changes made in the data of that table.  You can use this for auditing, data corrections and analysis.

MY CASE:

As some of you may be wondering why somebody would want to do this. This was a request from our dev team. The request was to move several system versioned tables from production to the development environment and to overwrite the existing data and structure. In this blog I’ll use 1 system versioned table as example. The table will be exactly the same on both databases but in real life it can of course be more complex.

WHAT I FOUND OUT:

Ok, before we really start, let us begin with setting up 2 databases. I will name these DB1 and DB2 and will be moving the data from DBA2 to DB1. In each database you can create your own system versioned table with a primary key. Fill it with some data or use the script provided below if you don’t feel like doing it yourself.

CREATE TABLE ExampleTable (

ID INT IDENTITY(1,1) PRIMARY KEY,

Salsify INT,

Tempeh INT,

Durian INT,

SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START,

SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END,

PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)

) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ExampleTable_X));

INSERT INTO ExampleTable (Salsify, Tempeh, Durian) VALUES (88, 38, 97);

INSERT INTO ExampleTable (Salsify, Tempeh, Durian) VALUES (1, 40, 96);

INSERT INTO ExampleTable (Salsify, Tempeh, Durian) VALUES (74, 26, 39);

INSERT INTO ExampleTable (Salsify, Tempeh, Durian) VALUES (89, 72, 96);

INSERT INTO ExampleTable (Salsify, Tempeh, Durian) VALUES (86, 54, 61);

UPDATE ExampleTable SET Salsify = 81 WHERE ID = 1;

UPDATE ExampleTable SET Durian = 5 WHERE ID = 2;

UPDATE ExampleTable SET Salsify = 87 WHERE ID = 3;

UPDATE ExampleTable SET Salsify = 15 WHERE ID = 4;

UPDATE ExampleTable SET Durian = 68 WHERE ID = 5;

And now we are ready.

Since we need to replace the full table the first task should be clear… DROP the table.

So let’s try:

DROP TABLE ExampleTable;

Yes, it does not work!

You cannot simply delete a temporal table, you first need to

SET SYSTEM VERIONING = OFF:

ALTER TABLE ExampleTable SET (SYSTEM_VERSIONING = OFF)

Now you can drop the table, but first let us take a look in object explorer and see if anything has changed. Yes, we now see an additional table. This will also need to be dropped.

Now that these have been dropped our next step will be to recreate the table from DB2 on DB1.
If you are smart, you will have prepared your script for this beforehand. Since I haven’t, I can now show you how I would do this.

I normally use Generate Scripts. Here you can in one go easily script out the indexes, constraints etc related to the table.

I do not change the default values here in advanced

As stated earlier you can go there to script constraints, triggers, data, …

In objects you select the table you want to script. I usually open in a new query window.

This should give you the following script:

USE [SYSTEMVERSIONING_DB1]

GO

/****** Object:  Table [dbo].[ExampleTable_X]     ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[ExampleTable_X](

[ID] [int] NOT NULL,

[Salsify] [int] NULL,

[Tempeh] [int] NULL,

[Durian] [int] NULL,

[SysStartTime] [datetime2](7) NOT NULL,

[SysEndTime] [datetime2](7) NOT NULL

) ON [PRIMARY]

GO

/****** Object:  Table [dbo].[ExampleTable]     ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[ExampleTable](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Salsify] [int] NULL,

[Tempeh] [int] NULL,

[Durian] [int] NULL,

[SysStartTime] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,

[SysEndTime] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,

PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],

PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])

) ON [PRIMARY]

WITH

(

SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[ExampleTable_X] )

)

GO

Go ahead and create this on DB1.

One remark about this script: It is important to create the history table first if you will join these directly, if not the GENERATED ALWAYS AS ROW START/END will give errors.

Now you will have the empty tables on DB1 so it’s just a simple matter of transferring the table, right? Right…

I like to use the Import and Export Wizard for transferring data in cases such as these.

After filling in the source and the target server you will be able to select the tables here

Be sure to enable identity insert since we have a primary key. Then “Next” until Finish and let’s see what we get.

Data has been inserted into the normal table but in the history table we cannot do a manual insert.

There, in the history table, new data will only be created when data on the original table data is being manipulated or created. So, we need to revise the creation of the tables on DB1. First we must not link the tables by enable the system versioning. We must wait with this until we have transferred the data.

Now let’s remove the system versioning from the table creation script.

USE [SYSTEMVERSIONING_DB1]

GO

/****** Object:  Table [dbo].[ExampleTable_X]   ******/

CREATE TABLE [dbo].[ExampleTable_X](

[ID] [int] NOT NULL,

[Salsify] [int] NULL,

[Tempeh] [int] NULL,

[Durian] [int] NULL,

[SysStartTime] [datetime2](7) NOT NULL,

[SysEndTime] [datetime2](7) NOT NULL

) ON [PRIMARY]

GO

/****** Object:  Table [dbo].[ExampleTable]    ******/

CREATE TABLE [dbo].[ExampleTable](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Salsify] [int] NULL,

[Tempeh] [int] NULL,

[Durian] [int] NULL,

[SysStartTime] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,

[SysEndTime] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL

PRIMARY KEY CLUSTERED

(

[ID] ASC

)

)

GO

And when you execute this script, you should get this error:

Msg 13509, Level 16, State 1, Line 14

Cannot create generated always column when SYSTEM_TIME period is not defined.

Seems like we forgot to remove GENERATED ALWAYS AS ROW START in the column definition, highlighted in the script. Keep this in mind when prepping scripts for more tables.

Once this has been done you can either try again as I did before or use this script. It gets rather tedious scripting out the data transfer, but you can save it and reuse it later if needed.

SET IDENTITY_INSERT [dbo].[ExampleTable] ON

INSERT INTO [dbo].[ExampleTable] (       [ID],

[Salsify],

[Tempeh],

[Durian],

[SysStartTime],

[SysEndTime]

)

SELECT [ID],

[Salsify],

[Tempeh],

[Durian],

[SysStartTime] ,

[SysEndTime]

FROM [SYSTEMVERSIONING_DB2].[dbo].[ExampleTable]

GO

INSERT INTO [dbo].[ExampleTable_X] ([ID],

[Salsify],

[Tempeh],

[Durian],

[SysStartTime],

[SysEndTime])

SELECT [ID],

[Salsify],

[Tempeh],

[Durian],

[SysStartTime],

[SysEndTime]

FROM [SYSTEMVERSIONING_DB2].[dbo].[ExampleTable_X]

SET IDENTITY_INSERT [dbo].[ExampleTable] OFF

GO

Here it is important not to forget using SET IDENTITY_INSERT ON/OFF but not all tables will need this. The history tables will not be using this.

Now that the data transfer has been done, we will need to enable the system versioning. Remember in the original script there was also the GENERATED ALWAYS AS ROW END/START that needs to be defined on the correct column. This script can be used:

ALTER TABLE dbo.ExampleTable

ADD PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)

ALTER TABLE dbo.ExampleTable

SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].ExampleTable_X, DATA_CONSISTENCY_CHECK = ON))

GO

And done! You have now transferred one system versioned table from one database to another. Congratulations.

Now we have seen the full process, here is a quick recap:

• Before being able to drop the table u need to set SYSTEM_VERSIONING OFF
• When scripting out the tables, adapt the scripts so you do not enable system versioning
• When transferring data do not forget to enable identity insert where relevant
• And last but not least, add a period for system_time and enable system versioning

Some additional remark:

If you have tried these things yourself, you probably did this with a user that is either SA or part of the db_owner role. If you do this with a user that has ALTER SCHEMA right you will be able to CREATE the table with system versioning defined right there but you won’t be able to do ALTER TABLE SET (SYSTEM_VERSIONING = ON/OFF).

For this you will need at least CONTROL rights. Of course, we do not want to give CONTROL permissions to everyone because this enables them to also do DROP DATABASE and only a select few of elites should be able to do this.

I hope it was useful for you and you learned something.

Until my next blog!

Joris