Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

Recover a SQL Server database prior to database changes


By:   |   Last Updated: 2016-09-16   |   Comments   |   Related Tips: 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.

Screen capture of inserted rows.

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.

Marked Transaction with bogus code.

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.

New rows on logmarkhistory table.

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.

Both SQL Server databases restored with the marked transaction
Next Steps


Last Updated: 2016-09-16


next webcast button


next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools