Read time 7 minutes
With SQL Server 2016, Microsoft introduced “Temporal Tables,” also known as “System-Versioned Temporal Tables,” it’s such a feature that brought the built-in support for gathering information about the data stored in tables at any time.
System-versioned temporal tables are very useful, as they provide rich information about the data itself (stored in the database) and any change (update or edit) reflected on the table than just showing the current data in the current time slot.
In this write-up, we’ll walk you through all about Temporal Tables (System-Versioned temporal tables), how it works, how to query data from a temporal table, and most importantly how to restore data with a SQL server temporal table.
What is a temporal table?
A Temporal Table or system-versioned temporal table is a user table designed to store a full history of changes reflected on the data in the database, and this record of change in data allows inspection by the admin or the DBA at any time.
A temporal table has two columns (period columns) each defined with a datetime2 data type – SysStartTime & SysEndTime and ascribed as period columns. The system uses the two table columns to store, a period of validity, for every specific row which either is modified or has undergone through a single change.
Adding to the period columns, a reference to a second table with the mirrored schema is also incorporated by the temporal table, this second table is known or referenced as “History Table.” The user holds the option to either select an existing history table or allow the system to create a default history table.
How does a temporal table work in SQL server database?
Implementation of system-versioning for a table is done with a pair of tables, the first one is a current table, and the other is a history table. And as said above, for every specific row, the two datetime2 data type period columns are used to define the period of validity.
SysStartTime also denoted as period start column, for each row stores the start time, whereas SysEndTime also denoted as period end column, for each row stores the end time.
The below illustration demonstrates that the current value for every specific row is stored in the current table and the history table holds previous values for all rows, including the period start & end time for which is valid.
Below example illustrates how to create a table, including the implementation of period columns to record the period of validity for each row:
(
[UserID] int NOT NULL PRIMARY KEY CLUSTERED
, [NAME] nvarchar(100) NOT NULL
, [LEVEL] varchar(100) NOT NULL
, [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START
, [ValidTo] datetime2 (2) GENERATE ALWAYS AS ROW END
, PERIOD FOR SYSTEM_Time (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.UserHistory));
Above, a database table is created to store user information with period columns implemented to record the period validity by storing both the start & end time for each row. Further, you can perform INSERT, UPDATE, DELETE, and MERGE queries on the table.
How to query temporal data from a SQL server temporal table?
The below example illustrates how to query temporal data from a SQL server temporal table.
FOR SYSTEM_TIME
BETWEEN ‘2019-04-10 00:00:00.0000000’ AND ‘2019-04-15 00:00:00.0000000’
WHERE UserID = 500 ORDER BY ValidFrom;
In the query given above, we’ve queried for SYSTEM_TIME by specifying a date range in respect to the pre-defined UserID and finally sorted the queue data according to the ValidFrom period column.
The workflow of a basic temporal data query is shown in the representation below.
.
Restore data with a SQL server temporal table
Restoring data with a SQL Server temporal table is also possible by determining the state of data at a particular time in the past. Recovering records turns out very helpful specifically when the need to recover select or reconstruction of an entire table surfaces.
Below is the step-by-step record recovery process to follow:
Review Changes Made to the Table
DECLARE @EndDate datetime
SET @StartDate=’2019-04-10 00:00:00’
SET @EndDate=’2019-04-15 23:59:59’
SELECT * FROM [Users] FOR system_time between @StartDate and @EndDate
WHERE Name = ‘Richard’
Once the query executes, you’ll be given with a time point informing if the record was deleted or a change was made to the table, now to recover the record that’s missing or changed proceed to the next step.
Create Query to Retrieve Record using AsOF
The below query is to retrieve the table record from a specific point of time by using the sub-clause AsOF.
FROM [Users] FOR SYSTEM_TIME AS OF ‘2019-04-12 10:20:00’
WHERE Name=’Richard’
Create Temp Table to Insert the Record
The below query inserts the data (table record) found deleted into the temp table after creating it.
SELECT CDC_DemoID, name, Address, City
FROM [Users] FOR SYSTEM_TIME AS OF ‘2019-04-12 10:20:00’
WHERE Name=’Richard’
Set Identity Insert ON and Insert the Deleted Record and Disable Identity Insert then
With the query given below, we’ve first enabled the identity insert for [dbo].[Users], as the table has an identity, and then, insert the deleted record in the table by fetching the record from temp table we created in the last step and disabled the identity insert by setting the status to OFF.
INSERT INTO [Users] (CDC_DemoID, name, Address, City)
SELECT CDC_DemoID, name, Address, City
FROM #TempTable
SET IDENTITY_INSERT [Users] OFF
Verify that Record has been Restored
Now, it’s important that you verify that the deleted record has been restored successfully, to do that run the query given below.
Upon executing the above available above, you can verify that the deleted record is now restored and available again.
Emphasizing in short of what we’ve shared above, you now know how a Temporal Table works, how to query Temporal data, and how to retrieve deleted record with a Temporal Table.
Kernel for SQL Database Recovery
Given that our context is all about the temporal table and how to restore a missing or deleted record by analyzing the point of time in the past using the history table successfully, it is apt to discuss about SQL database corruption as well.
It’s frightening to even think of cases when you cannot get the lost data such as a single database table, specific database objects, or sub-tables back. Database file corruption, broken file, damaged data, inaccessible files, etc. are found to be some of the many reasons behind such disasters.
In scenarios like so, we recommend using a SQL Recovery software that helps you in getting the corrupt database file back to the normal state.
Along with repairing the damaged MDF/NDF database file(s), the software allows you to create database backups scripts once it is restored, generate live data preview, open and view MDF files without SQL Server, and more.
Wrap up
Kernel for SQL Database recovery is an advance and smart utility that helps the users in many use cases like how to backup SQL server database, how to recover SQL database without backup and more.
