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

Posted by Mladen Prajdic on SQL Team See other posts from SQL Team or by Mladen Prajdic
Published on Wed, 20 Oct 2010 12:38:15 GMT Indexed on 2010/12/06 16:58 UTC
Read the original article Hit count: 418

Filed under:

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.

© SQL Team or respective owner