Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Transactions: Creating a single restore point across multiple databases.

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

TransactionMark1

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.

 

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.


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

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...