This is a disaster and recovery trick I’ve found to be useful for developers with batch processes that hit multiple databases. If you have read up much on either the BEGIN TRANSACTION or RESTORE statements you will probably have noticed the MARK option. If you mark a transaction in the log file of a database then you have the option of restoring that database either before or at the mark. This does require that the database be in either the full or bulk logged recovery model because you have to be able to take transaction log backups. The particular use for this that I’m discussing involves creating marked transactions in multiple databases then if you have a situation that requires you recover one or more of the databases you can recover all of them to the exact same point.

So for example you have databases A, B and C. You have a batch process at night that updates data in all three databases. Right at the beginning of your batch process, or perhaps at various checkpoints in the process, you create a marked transaction in each of the databases (or one or more distributed transactions) that inserts a row into a table (for example a table that says “Batch 123 has started”) in each of the databases and then commits the transaction. Then during your batch process your instance crashes. Assuming that you didn’t have just one big transaction for the entire process (not something I would typically recommend) you now have several databases that are potentially out of sync with each other and at the very least you don’t know where your process was at the precise moment of the crash. So how does the mark help? With marked transactions in place you can restore all three databases back to the beginning of the batch, or one of your checkpoints, using the RESTORE STOPBEFOREMARK.

-- Set up for the test
USE master;
GO
CREATE DATABASE DatabaseA;
CREATE DATABASE DatabaseB;
CREATE DATABASE DatabaseC;
GO
USE DatabaseA;
GO
CREATE TABLE BatchList (Id Int NOT NULL IDENTITY(1,1), BatchDate DateTime);
GO
CREATE TABLE TableA (Col1 varchar(10), Col2 varchar(10));
GO
USE DatabaseB;
GO
CREATE TABLE BatchList (Id Int NOT NULL IDENTITY(1,1), BatchDate DateTime);
GO
CREATE TABLE TableB (Col1 varchar(10), Col2 varchar(10));
GO
USE DatabaseC;
GO
CREATE TABLE BatchList (Id Int NOT NULL IDENTITY(1,1), BatchDate DateTime);
GO
CREATE TABLE TableC (Col1 varchar(10), Col2 varchar(10));
GO

-- Initial backups for all of the databases
BACKUP DATABASE DatabaseA TO DISK = 'Y:\MybackupFolder\DatabaseA.bak';
BACKUP DATABASE DatabaseB TO DISK = 'Y:\MybackupFolder\DatabaseB.bak';
BACKUP DATABASE DatabaseC TO DISK = 'Y:\MybackupFolder\DatabaseC.bak';
GO

-- Create a marked transaction in each database
DECLARE @MarkName varchar(100);
SET @MarkName = 'Batch started at ' + CAST(getdate() AS varchar(20));
BEGIN TRANSACTION BatchStart
   WITH MARK @MarkName;
INSERT INTO DatabaseA.dbo.BatchList (BatchDate) VALUES (GetDate());
INSERT INTO DatabaseB.dbo.BatchList (BatchDate) VALUES (GetDate());
INSERT INTO DatabaseC.dbo.BatchList (BatchDate) VALUES (GetDate());
COMMIT;
GO

Because all three databases in my example are on a single instance creating a distributed transaction that touched all three of the databases at once was easy. If one or more of the databases were on different instances then I would have had to deal with it slightly differently. In the first “additional reading” link below there is a good example of using a stored procedure to push the transaction to other instances.

Once a marked transaction is committed then an entry for each of the databases touched is entered into logmarkhistory

SELECT * FROM msdb.dbo.logmarkhistory;
GO

Since we are running a multi hour batch process and we take regular log backups we can hope that at least one set of log backups will occur during our process.

-- Log backups
BACKUP LOG DatabaseA TO DISK = 'C:\Backups\DatabaseA_Log1.bak';
BACKUP LOG DatabaseB TO DISK = 'C:\Backups\DatabaseB_Log1.bak';
BACKUP LOG DatabaseC TO DISK = 'C:\Backups\DatabaseC_Log1.bak';
GO

-- Part of the batch process
INSERT INTO DatabaseA.dbo.TableA VALUES ('A','B');
INSERT INTO DatabaseB.dbo.TableB VALUES ('C','D');
INSERT INTO DatabaseA.dbo.TableA VALUES ('E','F');
INSERT INTO DatabaseC.dbo.TableC VALUES ('G','H');
INSERT INTO DatabaseA.dbo.TableA VALUES ('I','J');
INSERT INTO DatabaseB.dbo.TableB VALUES ('K','L');
INSERT INTO DatabaseB.dbo.TableB VALUES ('M','N');
INSERT INTO DatabaseC.dbo.TableC VALUES ('O','P');
GO

About an hour into the batch process DatabaseB goes suspect. At this point we could do a point in time recovery. However if there are several batch processes running one after another, we have several checkpoints in the process or for whatever other reason we aren’t 100% certain of the time the batch started then doing a point in time recovery isn’t the best option. Which is of course why we set up the marked transaction in the first place. We now do a RESTORE STOPBEFOREMARK on each database and we are right back at the beginning of the batch, or checkpoint, ready to try again.

USE master;
GO
RESTORE DATABASE DatabaseA FROM DISK = 'C:\Backups\DatabaseA.bak' WITH REPLACE, NORECOVERY;
RESTORE LOG DatabaseA FROM DISK = 'C:\Backups\DatabaseA_Log1.bak' WITH  
STOPBEFOREMARK = 'Batch started at Jan 28 2014  9:09PM';
RESTORE DATABASE DatabaseA WITH RECOVERY;
RESTORE DATABASE DatabaseB FROM DISK = 'C:\Backups\DatabaseB.bak' WITH REPLACE, NORECOVERY;
RESTORE LOG DatabaseB FROM DISK = 'C:\Backups\DatabaseB_Log1.bak' WITH 
STOPBEFOREMARK = 'Batch started at Jan 28 2014  9:09PM';
RESTORE DATABASE DatabaseB WITH RECOVERY;
RESTORE DATABASE DatabaseC FROM DISK = 'C:\Backups\DatabaseC.bak' WITH REPLACE, NORECOVERY;
RESTORE LOG DatabaseC FROM DISK = 'C:\Backups\DatabaseC_Log1.bak' WITH  
STOPBEFOREMARK = 'Batch started at Jan 28 2014  9:09PM';
RESTORE DATABASE DatabaseC WITH RECOVERY;
GO

For additional reading here are some BOL links I found on the same subject.

• Use Marked Transactions to Recover Related Databases Consistently (Full Recovery Model)
• Recovery of Related Databases That Contain Marked Transaction

Transactions are a big subject which I’m going to explore over several posts. I am by no means going to cover the subject exhaustively but if you have any subjects you would like me to cover or think I’ve missed something feel free to comment or email me.

• Transactions: Rolling back a nested transaction.
• Transactions: Rolling back part of a transaction.
• Transactions: Rolling back a transaction inside a stored procedure.
• Transactions: What are they?
• Transactions: Who, What and Where
• Transactions: What commands aren’t allowed.

Filed under: Backups, Microsoft SQL Server, SQLServerPedia Syndication, Transactions Tagged: backups, code language, language sql, microsoft sql server, T-SQL, transactions