By: Daniel Farina | Comments (1) | Related: More > Database Administration
Problem
You are about to deploy a critical change in your application to the SQL Server databases. The code is enclosed in an explicit SQL Server transaction, but even when the code commits successfully there may be an issue which will require you to restore the database to the point before the changes were made. In this tip I will show you how transaction marking can be used to bring the application’s databases back to a prior state.
Solution
Sometimes we as DBAs face a situation when we are asked to deploy an application upgrade that performs radical changes to the data structures and the application’s behavior. In such cases you have three possible scenarios:
- The scripts ends successfully and the application works as expected.
- The scripts ends in error and the transaction is rolled back, like nothing ever happened.
- The scripts ends successfully, but the application does not work as expected.
The last is the worst case scenario and we must be prepared for such event. Even though changes made that disrupt the normal behavior for the application is the developer's responsibility, it’s our responsibility as Database Administrators to bring things back to normal as fast as we can.
If you ask the developer for a rollback procedure his/her answer may be that the only way to bring things back to normal is with a full database restore. This looks very simple for a DBA, but what if the deployment affects more than one database? In such case, the involved databases must be in a logical consistent state. To overcome this situation SQL Server offers us what is called “Marked Transactions” which we will look at to help us solve this possible issue.
What is a SQL Server Marked Transaction?
Marked Transactions are transactions like we are all familiar with, but are stored in the transaction log with a name and in the dbo.logmarkhistory table of the msdb database. To create a marked transaction you need to assign a name to the transaction and add the WITH MARK keyword as shown below.
BEGIN TRANSACTION SomeName WITH MARK
When you need to restore a database to a marked transaction you have to restore the last full backup and the transaction log backups until the marked transaction. You have the chance to recover the database to the mark including the marked transaction by using the WITH STOPATMARK clause. On the other hand, if you need to recover to the moment previous to the mark you use the WITH STOPBEFOREMARK clause. In order to recover to a transaction mark, the databases must be in the Full Recovery mode.
Sample Implementation Using SQL Server Marked Transactions
I will show a simple case in which we can use marked transactions to recover two databases. Let’s start by creating two databases, Sales and Employees.
USE [master] GO CREATE DATABASE [Employees] CONTAINMENT = NONE ON PRIMARY ( NAME = N'Employees', FILENAME = N'E:\MSSQL\Employees.mdf' , SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'Employees_log', FILENAME = N'E:\MSSQL\Employees_log.ldf' , SIZE = 10MB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB) GO ALTER DATABASE [Employees] SET RECOVERY FULL GO CREATE DATABASE [Sales] CONTAINMENT = NONE ON PRIMARY ( NAME = N'Sales', FILENAME = N'E:\MSSQL\Sales.mdf' , SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'Sales_log', FILENAME = N'E:\MSSQL\Sales_log.ldf' , SIZE = 10MB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB) GO ALTER DATABASE [Sales] SET RECOVERY FULL GO
On the Employees database create a table named Sellers that will contain seller’s name, its commissions percentage over sales and the accumulated commissions. Then insert two employees.
USE [Employees];
GO
CREATE TABLE Sellers
(
SellerID INT IDENTITY(1, 1)
PRIMARY KEY ,
Name VARCHAR(255) ,
Percentage NUMERIC(3, 2) ,
Commissions MONEY
)
GO
INSERT INTO dbo.Sellers
SELECT 'John Doe', 0.25, 0
UNION ALL
SELECT 'Linda Smith', 0.1, 0
GO
On the Sales database create a table named Transactions to store sales information.
USE [Sales];
GO
CREATE TABLE Transactions
(
TransactionID INT IDENTITY(1, 1)
PRIMARY KEY ,
SellerID INT ,
Amount MONEY
)
GO
Then create a trigger on the Transactions table to update the sellers table accumulated commissions when a new row is inserted.
USE Sales;
GO
CREATE TRIGGER TR_ComputeCommisssions ON Transactions
AFTER INSERT
AS
BEGIN
UPDATE [Employees].dbo.Sellers
SET Commissions = S.Commissions + I.Amount * S.Percentage
FROM inserted I
INNER JOIN [Employees].dbo.Sellers S ON S.SellerID = I.SellerID
END
Take a full database backup of both databases.
USE master; GO BACKUP DATABASE Sales TO DISK = 'E:\Backups\Sales.bak'; GO BACKUP DATABASE Employees TO DISK = 'E:\Backups\Employees.bak'; GO
Now insert a few rows in table Transactions and take a look at the Sellers table for changes in the Commissions column.
USE Sales; GO INSERT INTO dbo.Transactions ( SellerID, Amount ) VALUES ( 1, 100) INSERT INTO dbo.Transactions ( SellerID, Amount ) VALUES ( 2, 200) INSERT INTO dbo.Transactions ( SellerID, Amount ) VALUES ( 1, 5600) GO SELECT * FROM Employees..Sellers; GO
If everything is fine you should get results like below.
Now we are going to use a marked transaction to update the Sellers table and insert some rows on the Transactions table. Notice that the update statement does not have a WHERE clause in order to simulate what could be a bogus deploy.
USE Sales; BEGIN TRANSACTION TX_Mark WITH MARK UPDATE Employees..Sellers SET Percentage = 0 INSERT INTO dbo.Transactions ( SellerID, Amount ) VALUES ( 1, 9999) INSERT INTO dbo.Transactions ( SellerID, Amount ) VALUES ( 2, 9999) INSERT INTO dbo.Transactions ( SellerID, Amount ) VALUES ( 1, 9999) GO COMMIT TRANSACTION TX_Mark GO SELECT * FROM Employees..Sellers; GO
As you can see on the next image the Sellers table didn’t sum the commissions of the last inserted rows on Transactions table, because the percentage column is set to zero.
If you check the dbo.logmarkhistory table in the msdb database you will see that a row has been added for each database involved in the marked transaction.
In order to recover both databases to the time before the marked transaction you need to take a backup of the transaction log, in this case it’s a tail log backup.
USE master; GO BACKUP LOG Sales TO DISK = 'E:\Backups\Sales.trn' WITH NORECOVERY; GO BACKUP LOG Employees TO DISK = 'E:\Backups\Employees.trn' WITH NORECOVERY; GO
After taking the log backup you have to restore the last full databases backup without recovery and then restore the logs with the option STOPBEFOREMARK and the name of the marked transaction as shown below.
RESTORE DATABASE Sales FROM DISK = 'E:\Backups\Sales.bak' WITH NORECOVERY; GO RESTORE DATABASE Employees FROM DISK = 'E:\Backups\Employees.bak' WITH NORECOVERY; GO RESTORE LOG Sales FROM DISK = 'E:\Backups\Sales.trn' WITH STOPBEFOREMARK = 'TX_Mark', RECOVERY; GO RESTORE LOG Employees FROM DISK = 'E:\Backups\Employees.trn' WITH STOPBEFOREMARK = 'TX_Mark', RECOVERY; GO
As you can see on the next image both databases were recovered to the state before the marked transaction.
Next Steps
- Based on this technique you could use a transaction name when you deploy databases changes and restore prior to the changes being deployed if there was a need to roll back.
- If you are new to restoring a database this tutorial is a good starting point: SQL Server Restore Options and Commands Tutorial.
- For more information about transactions and their relation with the transaction log take a look at this tutorial: Introduction to the SQL Server Transaction Log Tutorial.
- There are other ways to restore a database to a specific time without using marked transactions: SQL Server point in time recovery.
About the author
Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
