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.

-- TAKE A FULL BACKUP of the databseBACKUPDATABASE TestTxMark1 TODISK = 'c:\TestTxMark1.bak'GO

USE master GOCREATEDATABASE TestTxMark2GO

USE TestTxMark2GOCREATETABLE TestTable2( ID INT,VALUE UNIQUEIDENTIFIER)-- insert some data into the table so we can have a starting pointINSERT INTO TestTable2SELECT ROW_NUMBER() OVER(ORDERBY number) AS RN, NEWID()FROM master..spt_valuesORDERBY RN

SELECT *FROM TestTable2GO

-- TAKE A FULL BACKUP of our databseBACKUPDATABASE TestTxMark2 TODISK = 'c:\TestTxMark2.bak'GO

-- restore the log backup to the transaction markRESTORE LOG TestTxMark1 FROMDISK = 'c:\TestTxMark1.trn'WITH RECOVERY, -- recover to state before the transaction STOPBEFOREMARK = 'TxDb';-- recover to state after the transaction-- STOPATMARK = 'TxDb';GO

-- restore the log backup to the transaction markRESTORE LOG TestTxMark2 FROMDISK = '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 tableSELECT * FROM TestTable1

USE TestTxMark2-- we restored to time before the transaction -- so we DON'T have NULL values in our tableSELECT * 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.

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.