There seems to be some confusion out there in the internet about how to implement transaction marks. Or perhaps I was merely very confused about how to implement them.
Transaction marks allow you to restore two or more database to a consistent point in time. For example if you have two database and your app writes to both databases, how do you restore them to a consistent point in time without kicking all your users off the databases? This is complicated by many things, for example backup speed.
I googled looking for a working example but could not find one, so here is a script I wrote to demonstrate it.
create database MarkedTransaction
GO
create database MarkedTransaction1
GO
use MarkedTransaction
go
create table test1(pk int)
GO
use MarkedTransaction1
GO
create table test1(pk int)
GO
backup database MarkedTransaction to disk='c:\MarkedTransaction.bak' with init
GO
backup database MarkedTransaction1 to disk='c:\MarkedTransaction1.bak'with init
GO
begin transaction
insert into MarkedTransaction.dbo.test1(pk) values(1)
insert into MarkedTransaction1.dbo.test1(pk) values(1)
commit tran
GO
begin transaction mark6 with mark 'marked transaction'
insert into MarkedTransaction.dbo.test1(pk) values(2)
insert into MarkedTransaction1.dbo.test1(pk) values(2)
commit transaction mark6
GO
begin transaction mark7 with mark 'marked transaction - 1'
insert into MarkedTransaction.dbo.test1(pk) values(3)
insert into MarkedTransaction1.dbo.test1(pk) values(3)
commit tran mark7
GO
backup log MarkedTransaction to disk='c:\MarkedTransactionlog.bak' with init
backup log MarkedTransaction1 to disk='c:\MarkedTransactionlog1.bak' with init
GO
use master
GO
--backing up the tail
backup log MarkedTransaction to disk='c:\MarkedTransactionlogTail.bak' with init, norecovery
backup log MarkedTransaction1 to disk='c:\MarkedTransactionlog1Tail.bak' with init, norecovery
GO
--restoring the database
restore database markedtransaction from disk='c:\markedtransaction.bak' with norecovery
restore database markedtransaction1 from disk='c:\markedtransaction1.bak' with norecovery
GO
--restoring the log
RESTORE LOG MarkedTransaction FROM
DISK = 'c:\MarkedTransactionlog.bak'
WITH STOPATMARK = 'mark6', recovery
GO
RESTORE LOG MarkedTransaction1 FROM
DISK = 'c:\MarkedTransactionlog1.bak'
WITH STOPATMARK = 'mark6', recovery
GO
select * from MarkedTransaction.dbo.test1
select * from MarkedTransaction1.dbo.test1
--both 1, 2 are there, 3 is not
--repeating, but this time we will stop before
backup database MarkedTransaction to disk='c:\MarkedTransaction.bak' with init
GO
backup database MarkedTransaction1 to disk='c:\MarkedTransaction1.bak'with init
GO
begin transaction
insert into MarkedTransaction.dbo.test1(pk) values(6)
insert into MarkedTransaction1.dbo.test1(pk) values(6)
commit transaction
GO
begin transaction mark8 with mark 'marked transaction -2'
insert into MarkedTransaction.dbo.test1(pk) values(7)
insert into MarkedTransaction1.dbo.test1(pk) values(7)
commit transaction mark8
GO
begin transaction
insert into MarkedTransaction.dbo.test1(pk) values(8)
insert into MarkedTransaction1.dbo.test1(pk) values(8)
commit transaction
GO
backup log MarkedTransaction to disk='c:\MarkedTransactionlog.bak' with init
backup log MarkedTransaction1 to disk='c:\MarkedTransactionlog1.bak' with init
GO
use master
GO
backup log MarkedTransaction to disk='c:\MarkedTransactionlogTail.bak' with init, norecovery
backup log MarkedTransaction1 to disk='c:\MarkedTransactionlog1Tail.bak' with init, norecovery
GO
RESTORE Database MarkedTransaction from disk='c:\markedtransaction.bak' with norecovery
RESTORE Database MarkedTransaction1 from disk='c:\markedtransaction1.bak' with norecovery
GO
RESTORE LOG MarkedTransaction FROM
DISK = N'c:\MarkedTransactionlog.bak'
WITH STOPBEFOREMARK = N'mark8' , recovery
GO
RESTORE LOG MarkedTransaction1 FROM
DISK = N'c:\MarkedTransactionlog1.bak'
WITH STOPBEFOREMARK = N'mark8' , recovery
GO
select * from MarkedTransaction.dbo.test1
select * from MarkedTransaction1.dbo.test1
--both 1, 2, and 6 are there, 7 is not