I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

SQL Server Transaction Marks: Restoring multiple databases to a common relative point

We’re all familiar with the ability to restore a database to point in time using the RESTORE WITH STOPAT statement.

But what if we have multiple databases that are accessed from one application or are modifying each other? And over multiple instances? And all databases have different workloads? And we want to restore all of the databases to some known common relative point? The catch here is that this common relative point isn’t the same point in time for all databases. This common relative point in time might be now in DB1, now-1 hour in DB2 and yesterday in DB3. And we don’t know the exact times.

Let me introduce you to Transaction Marks. When we run a marked transaction using the WITH MARK option a flag is set in the transaction log and a row is added to msdb..logmarkhistory table. When restoring a transaction log backup we can restore to either before or after that marked transaction. The best thing is that we don’t even need to have one database modifying another database. All we have to do is use a marked transaction with the same name in different database.

Let’s see how this works with an example. The code comments say what’s going on.

USE master 
GO
CREATE DATABASE TestTxMark1
GO

USE TestTxMark1
GO
CREATE TABLE TestTable1
(
ID INT,
VALUE UNIQUEIDENTIFIER
)
-- insert some data into the table so we can have a starting point

INSERT INTO TestTable1
SELECT ROW_NUMBER() OVER(ORDER BY number) AS RN, NULL
FROM master..spt_values
ORDER BY RN

SELECT *
FROM TestTable1
GO

-- TAKE A FULL BACKUP of the databse
BACKUP DATABASE TestTxMark1 TO DISK = 'c:\TestTxMark1.bak'
GO
USE master 
GO
CREATE DATABASE TestTxMark2
GO

USE TestTxMark2
GO
CREATE TABLE TestTable2
(
ID INT,
VALUE UNIQUEIDENTIFIER
)
-- insert some data into the table so we can have a starting point
INSERT INTO TestTable2
SELECT ROW_NUMBER() OVER(ORDER BY number) AS RN, NEWID()
FROM master..spt_values
ORDER BY RN

SELECT *
FROM TestTable2
GO

-- TAKE A FULL BACKUP of our databse
BACKUP DATABASE TestTxMark2 TO DISK = 'c:\TestTxMark2.bak'
GO
-- start a marked transaction that modifies both databases
BEGIN TRAN TxDb WITH MARK
-- update values from NULL to random value
UPDATE TestTable1
SET VALUE = NEWID();
-- update first 100 values from random value
    -- to NULL in different DB
UPDATE TestTxMark2.dbo.TestTable2
SET VALUE = NULL
WHERE ID <= 100;
COMMIT
GO
 
 

-- some time goes by here 
-- with various database activity...



-- We see two entries for marks in each database. 
-- This is just informational and has no bearing on the restore itself.
SELECT * FROM msdb..logmarkhistory
image
USE master
GO
-- create a log backup to restore to mark point
BACKUP LOG TestTxMark1 TO DISK = 'c:\TestTxMark1.trn'
GO
-- drop the database so we can restore it back
DROP DATABASE TestTxMark1
GO
USE master
GO
-- create a log backup to restore to mark point
BACKUP LOG TestTxMark2 TO DISK = 'c:\TestTxMark2.trn'
GO
-- drop the database so we can restore it back
DROP DATABASE TestTxMark2
GO
-- RESTORE THE DATABASE BACK BEFORE OUR TRANSACTION
-- restore the full backup
RESTORE DATABASE TestTxMark1
FROM DISK = 'c:\TestTxMark1.bak'
WITH NORECOVERY;

-- restore the log backup to the transaction mark
RESTORE LOG TestTxMark1 FROM DISK = 'c:\TestTxMark1.trn'
WITH RECOVERY,
-- recover to state before the transaction
STOPBEFOREMARK = 'TxDb';
-- recover to state after the transaction
-- STOPATMARK = 'TxDb';
GO
-- RESTORE THE DATABASE BACK BEFORE OUR TRANSACTION
-- restore the full backup
RESTORE DATABASE TestTxMark2
FROM DISK = 'c:\TestTxMark2.bak'
WITH NORECOVERY;

-- restore the log backup to the transaction mark
RESTORE LOG TestTxMark2 FROM DISK = 'c:\TestTxMark2.trn'
WITH RECOVERY,
-- recover to state before the transaction
STOPBEFOREMARK = 'TxDb';
-- recover to state after the transaction
-- STOPATMARK = 'TxDb';
GO
USE TestTxMark1
-- we restored to time before the transaction
-- so we have NULL values in our table
SELECT * FROM TestTable1
USE TestTxMark2
-- we restored to time before the transaction
-- so we DON'T have NULL values in our table
SELECT * FROM TestTable2

 

Transaction marks can be used like a crude sync mechanism for cross database operations. With them we can mark our databases with a common “restore to” point so we know we have a valid state between all databases to restore to.

Print | posted on Wednesday, October 20, 2010 8:00 AM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: SQL Server Transaction Marks: Restoring multiple databases to a common relative point

Mladen,

Good topic and post!

I seem to recall that Microsoft BizTalk uses transaction marks to coordinate recovery points for its multiple DBs app design approach. See link: msdn.microsoft.com/.../aa577848%28BTS.70%29.aspx

Because SharePoint uses a multiple DB app design approach very similar to the BizTalk multiple DB app design approach, I initially suspected that SharePoint may also use transaction marks to coordinate recovery points across its multiple DBs. I don't find any obvious hits on "sharepoint transaction mark", so I could be wrong on this assumption.

Anyone else know more on SharePoint and transaction marks? Care to share?

I also recall an issue with a third-party DB backup product not supporting transaction marks (no names! - and it was quite some time ago). I suspect that those issues are resolved, but it would be good to confirm with your third-party DB backup vendor and test for yourself.


Scott R.
10/20/2010 3:24 PM | Scott R.
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET