How to Recover Data from a SQL Server Temporal Table

By:   |   Updated: 2018-01-18   |   Comments (1)   |   Related: > Disaster Recovery


Problem

Using a SQL Server system versioned temporal table we can reconstruct the entire table or we can restore specific records from table. In this tip I have demonstrated the process of recovering a deleted record from a SQL Server temporal table.

Solution

High-Level Introduction to SQL Server Temporal Tables

SQL Server 2014 introduced a new feature - system versioned temporal table. Using a temporal table, we can:

  1. View the state of table at specific point of time
  2. Perform DML statement audit
  3. Perform record level recovery

When we create a temporal table, we must declare one period for SYSTEM_TIME with two columns declared as GENERATED ALWAYS AS ROW START OR GENERATED ALWAYS AS ROW END. Both should have a datetime2 datatype. A history table must be declared, which should be schema-aligned with the current temporal table. They should have the same number of columns, column datatypes and column names. If we do not create a history table, then it will be created automatically in the current schema.

When we execute any DML statement on a temporal table, the below operations will be performed to maintain the data changes.

  1. The timestamp of the DML statement will be stored in period columns.
  2. If any Update or Delete statement is executed on a temporal table, the old values will be stored in the history table.

How period columns will be updated when we execute DML statements

Now I will demonstrate how StartTime and EndTime will be changed when we execute any DML statement on a temporal table. I have divided it into three parts:

  1. Changes in period columns and history table when we insert data.
  2. Changes in period columns and history table when we update data
  3. Changes in period columns and history table when we delete data.

First, I have created temporal table named User and its history table named User_Audit.  Below is code to create the temporal table. 

USE DemoDatabase
GO

BEGIN
    IF ((SELECT temporal_type FROM SYS.TABLES WHERE object_id = OBJECT_ID('dbo.User', 'U')) = 2)
    BEGIN
        ALTER TABLE [dbo].[User] SET (SYSTEM_VERSIONING = OFF)
    END
    DROP TABLE IF EXISTS [dbo].[User]
END
GO

CREATE TABLE [dbo].[User]
(
    [CDC_DemoID] INTEGER NOT NULL IDENTITY(1, 1) Primary Key clustered ,
    [Name] VARCHAR(255) NULL,
    [Address] VARCHAR(255) NULL,
    [City] VARCHAR(255) NULL,
    [Country] VARCHAR(100) NULL,
    StartTime datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL , 
    EndTime   datetime2(7) GENERATED ALWAYS AS ROW END  NOT NULL ,
    PERIOD FOR SYSTEM_TIME(StartTime, EndTime)
)
WITH
(
    SYSTEM_VERSIONING = ON 
    (
        HISTORY_TABLE = [dbo].[User_Audit],
        DATA_CONSISTENCY_CHECK = ON
    )
)
GO

Part 1: Insert Data

I have added 50 records in [dbo].[User] table and reviewed the values of the temporal table and the history table. The below code will add 50 rows to the table. 

INSERT INTO [dbo].[User]([Name],[Address],[City],[Country]) VALUES('Ryder','437-4246 Dui St.','Nederokkerzeel','Papua New Guinea'),('Buckminster','1996 Fermentum Avenue','Juazeiro do Norte','Tajikistan'),('Caesar','945-5738 Fusce Av.','San Francisco','Spain'),('Abel','P.O. Box 709, 8016 Vivamus Rd.','Colwood','French Polynesia'),('Lionel','Ap #463-4474 Luctus Ave','Lago Ranco','Eritrea'),('Martin','916-477 Libero St.','Deschambault','Jamaica'),('Brennan','8530 Et, Avenue','?om?a','United States'),('Jacob','Ap #199-199 Odio. Ave','Buren','Monaco'),('Brennan','7208 Tincidunt, Street','Wilmington','Saint Martin'),('Cairo','Ap #653-1857 Nunc Avenue','Massenhoven','United States');
WAITFOR DELAY '00:01';
INSERT INTO [dbo].[User]([Name],[Address],[City],[Country]) VALUES('Mannix','Ap #611-4150 Enim Street','Kawawachikamach','Equatorial Guinea'),('Abraham','370-5130 Ante Rd.','Elmshorn','Trinidad and Tobago'),('Timon','5777 Tincidunt Rd.','Bressoux','Saudi Arabia'),('Ishmael','P.O. Box 831, 4938 Velit Street','Candidoni','Ukraine'),('Kane','Ap #579-178 Nunc St.','Pollein','India'),('Driscoll','1400 Curae; Ave','Salvirola','Uganda'),('Hashim','P.O. Box 545, 7323 Urna. Ave','Banff','Mozambique'),('Yardley','Ap #428-4266 Libero Rd.','Tubeke Tubize','Saint Lucia'),('Ashton','Ap #817-4198 Vitae Avenue','Istres','Chad'),('Cooper','P.O. Box 899, 3456 Proin Street','Saint-Mard','Oman');
WAITFOR DELAY '00:01';
INSERT INTO [dbo].[User]([Name],[Address],[City],[Country]) VALUES('Macaulay','907-3796 Mi. St.','Ikot Ekpene','Montenegro'),('Hiram','227-3046 Vestibulum. Ave','Shimoga','El Salvador'),('Duncan','2368 Nisl St.','Ödemi?','Anguilla'),('Adam','6607 Tincidunt St.','Lidingo','Hungary'),('Noah','Ap #823-3339 Imperdiet St.','Amstelveen','Saint Kitts and Nevis'),('Alexander','7786 Sem Ave','South Bend','Romania'),('Paul','Ap #380-7033 Risus. Avenue','Olathe','Tunisia'),('Oleg','248-9509 Enim Ave','Holywell','Cape Verde'),('Kyle','Ap #508-6275 Nec, Road','Armadale','Guadeloupe'),('Gary','P.O. Box 193, 4072 Eget Rd.','Sudbury','Wallis and Futuna');
WAITFOR DELAY '00:01';
INSERT INTO [dbo].[User]([Name],[Address],[City],[Country]) VALUES('Igor','2699 Cubilia Ave','Wiesbaden','Sao Tome and Principe'),('Thane','Ap #434-1414 Aliquam Avenue','Gladstone','American Samoa'),('Arden','919-1768 Sit Rd.','Sint-Laureins-Berchem','Myanmar'),('Dalton','P.O. Box 506, 9740 Integer Rd.','Saint Louis','Ghana'),('Ferris','518-603 Ipsum. Rd.','Berlin','Denmark'),('Merritt','Ap #683-1099 Commodo Ave','Omaha','Cayman Islands'),('Hiram','P.O. Box 644, 8208 Et Avenue','Grado','Solomon Islands'),('Jonas','9943 Nisi Av.','Booischot','Serbia'),('Dillon','913-6970 Dolor Street','Saint Paul','Lesotho'),('Aaron','Ap #644-4739 Mauris. Avenue','Pak Pattan','French Guiana');
WAITFOR DELAY '00:01';

Then I executed a Select statement and reviewed the output of the temporal table and history table.

SELECT * FROM [dbo].[User] WHERE name='Ryder'

SELECT * FROM [dbo].[User_Audit] WHERE name='Ryder'
Output of User and User_Audit table. - Description: Screen Clipping

As shown in the above screenshot, the record insert time is stored in the “StartTime” column of [dbo].[Audit] table. The EndTime column will be changed when I have executed a delete statement.

Part 2: Update Data

Update the value of City column by executing the below query. 

UPDATE [dbo].[User] SET City='New Delhi' WHERE name='Ryder'

To see the output from [dbo]. [User] table, execute the following query. 

SELECT * FROM [dbo].[User] WHERE name='Ryder'

Output:

Result of User Table after update - Description: Screen Clipping

As you can see in the above screenshot, the value of the StartTime column has changed. The record update time is stored in the StartTime column.

To see the output from [dbo].[User_Audit], execute the following query. 

SELECT * FROM [User_Audit] where Name=’Ryder’

Output:

Output of User_Audit Table after update query - Description: Screen Clipping

To maintain the state of the row, SQL will store the old values of the record in the history table. As shown in the above screenshot, one row has been added in [dbo].[User_Audit] table. These are the old values before we ran the update query. The record insert time will be stored in the StartTime column and the record update time will be stored in EndTime column of [dbo].[User_Audit].

Part 3: Delete Data

Delete a row from the table by executing the below query. 

DELETE FROM [dbo].[User] WHERE name='Ryder'

To see the output from [dbo]. [User_Audit] table, execute the following query. 

SELECT * FROM [dbo].[User_Audit] WHERE Name=’Ryder’
Output of User_Audit table after delete query

Note: I executed an update and a delete statement on the same record, so the history table shows two rows.

Now when I ran the delete query, the values of StartTime and EndTime have changed. The new values of StartTime will be the record update time and EndTime will be the record delete time.

How to perform record recovery with SQL Server Temporal Tables

From the temporal table, we can determine the state of data at specific point of time in the past. This can be helpful when we need to recover specific records or reconstruct an entire table. We can use the “As Of” sub-clause to query specific data in the past. We can also use Between..And sub-class to get all the historical changes for a specific row in the current table.

In this example, first need to identify all the changes made on [dbo].[Audit] table. To review all the changes, execute the below query. 

DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate='2017-11-20 00:00:00'
SET @EndDate='2017-11-20 23:59:59'
SELECT * FROM [User] FOR system_time between @StartDate and @EndDate WHERE Name ='Ryder'

The query output is below.

Output of User table at specific point of time - Description: Screen Clipping

As you can see, the EndTime is 2017-11-20 12:46 PM. So, the record was deleted at 12:46 PM.

Now to recover the deleted record do the following:

Step 1

Create a query to retrieve the record at specific point of time by using the “AsOF” sub-clause. Below is the select query. 

SELECT CDC_DemoID, name, Address, City, Country 
FROM [User] FOR SYSTEM_TIME AS OF '2017-11-20 12:46:00' 
WHERE Name='Ryder' --Insert missing record in temp Table

Step 2

Create a temp table and insert the deleted record, retrieve using this query.

INSERT INTO #TempTable (CDC_DemoID, name, Address, City, Country) 
SELECT CDC_DemoID, name, Address, City, Country 
FROM [User] FOR SYSTEM_TIME AS OF '2017-11-20 12:46:00' 
WHERE Name='Ryder' --Insert missing record in temp Table

Step 3

The table has an identity, so we need to enable identity insert for [dbo].[User] table.  Then we Insert the deleted record in [dbo].[User] table from the temp table and disable identity insert.

SET IDENTITY_INSERT [user] ON
INSERT INTO [User](CDC_DemoID, name, Address, City, Country) 
SELECT CDC_DemoID, name, Address, City, Country 
FROM #TempTable -- Insert missing record in [User] table
SET IDENTITY_INSERT [User] OFF

Now, execute the below query to verify that our record has been restored successfully. 

SELECT * FROM [User] WHERE Name='Ryder'
Output of User table which has recovered record - Description: Screen Clipping

As you can see in the above screenshot, we have recovered our deleted record in [dbo].[User] table.

Summary

In above tip, I have covered the below topics:

  1. High-level introduction of temporal tables.
  2. How period columns (StartDate and EndDate) will be updated when we perform DML operations.
  3. How to perform a record level recovery.
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Nisarg Upadhyay Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional with more than 5 years of experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2018-01-18

Comments For This Article




Monday, June 8, 2020 - 11:27:13 AM - KayJay Back To Top (85857)

Thank you for this post.

However, shouldn't this

INSERT INTO #TempTable (CDC_DemoID, name, Address, City, Country) 
SELECT CDC_DemoID, name, Address, City, Country 
FROM [User] FOR SYSTEM_TIME AS OF '2017-11-20 12:46:00' 
WHERE Name='Ryder' --Insert missing record in temp Table

be corrected to this

SELECT CDC_DemoID, name, Address, City, Country 
INTO #TempTable (CDC_DemoID, name, Address, City, Country)
FROM [User] FOR SYSTEM_TIME AS OF '2017-11-20 12:46:00' 
WHERE Name='Ryder' --Insert missing record in temp Table

There was not a separate step to create the temporary table.

Thanks, 
Koen














get free sql tips
agree to terms