Undo operation in SQL Serve

We consider this scenario and implement undo capability in database.

We have developed a software for managing the data center. In this data center, we have all the information of personal, their finance documents, and all their working hours and so on.

After an elementary survey, we need at least the following Entity Relation Diagram (ERD) for data structure in a database and in this case, we use Microsoft SQL Server.

For implementing the undo in database, it has intended to use the Change Data Capture (CDC) feature of SQL Server.

What is CDC?

The CDC makes it possible to capture the data changing in a row in a table. For using CDC, the following steps must be followed.

1. SQL Server Agency must be active
2. Active CDC Service for the Database
3. Active CDC for each table, which its data must be captured.

SQL Server Agency must be started

To be sure if the SQL SERVER AGENCY is running or not we can use the following code to check the current state of SQL SERVER AGENCY.

EXEC xp_servicecontrol N'querystate',N'SQLServerAGENT'

If the service is stopped, we can START it with following Code.

EXEC xp_servicecontrol N'START',N'SQLServerAGENT'

In addition, for more information about the usage of xp_servicecontrol stored procedure the related link is inserted in related links selection.

Active CDC Service for the Database

By default, the CDC is not active in database because it is process and time consuming. However, it is useful for capturing changed data and implementing undo functionality. With following code CDC become active for Database.

USE DatabaseName 
GO 
EXEC sys.sp_cdc_enable_db 
GO 

Active CDC for each table

The CDC can become activated for all the columns of a table or for some of them. For starting the CDC the following code is used.

EXEC sys.sp_cdc_enable_table 
@source_schema = N'dbo', 
@source_name   = N'Table-Name', 
@role_name     = NULL 

Only four operations are done in a table or occurs for a data row, each of this operation has a number and is saved in __$operation column in CDC table.

After activate the CDC service a CDC table is created for the table under System Tables Folder with following format:

cdc.dbo_TableName_CT

Some points that must be known about CDC:

• The datatype NVARCHAR(MAX) cannot be captured.
• After applying a change in the table, which the CDC active, the CDC service become deactivate and must be activated again.

For more information about CDC, a related link is inserted in related links section.

The scenario for using the CDC

We should have a log file. This log is a table with the following columns.

In Logs table the actions that are done on the tables are saved. Each action has a row. Some actions can change some tables but it’s not important this action has a KeyWord, which says what have been done and what should have been redone, furthermore this Action has an ActionCode which is used to follow the action in different tables.

In this case we have three tables Person, Finance and WorkingHours. The ActionNames, which can occur are as follows:

As an example we insert a new person and make a change in person information and then we undo the changes. The above steps are demonstrated in figure 2, 3 and 4.

For each change a log-Record must be inserted in Logs table. The ID of the Logs table is the ActionCode which must be saved in related table.

References

Monitor, Start and Stop SQL Server services using xp_servicecontrol

Introduction to Change Data Capture (CDC) in SQL Server 2008

Azure SQL Database features