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

In Archive Tags #imported #archived Published 20. 10. 2010 1 comment

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.


Avtor: Anonymous, objavljeno na portalu SloDug.si (Arhiv)

Leave a comment

Please note that we won't show your email to others, or use it for sending unwanted emails. We will only use it to render your Gravatar image and to validate you as a real person.

HedaWhece
HedaWhece - nedelja, 10. november 2024

8 0 0 Total 164 100 124 40 <a href=https://fastpriligy.top/>priligy tablets online</a> it, she would really kill Zhao Ling