Backing Up Related Databases Using Marked Transactions

I am currently investigating how to safely back up the seven related databases that drive Microsoft Team Foundation Server. Microsoft is suspiciously vaigue and inconsistent about how to perform a safe TFS database backup.


This article states "Although SQL Server Management Studio allows you to back up individual databases at a time, restoring from such back ups can cause unexpected results because the databases are related and you risk restoring outdated versions."
Here, that requirement is strengthened (search for text in article 'Backing up Databases') "Because this data is stored in seven related databases, you might want to use marked transactions in the transaction logs of each database. Transaction-log marks help guarantee absolute consistency in the distributed data set."
but then it weakens the case again a few sentences later...
"Databases for Team Foundation Server are loosely coupled; any latency issues generally are limited to relationships between things such as work items and change sets that are created during the backup window."
There are various other TFS administrators with the same worry about backing up the TFS databases - see here and here.

This requirement to back up related databases to a consistent point in time is not new in Microsoft products. It seems that BizTalk server had exactly the same complication. The nice thing for the BizTalk administrators was that Microsoft included a stored procedure to manage backup of related databases in this way. Team Foundation server administrators don't seem to be so lucky.

I've read the documentation for distributed transaction marking but it doesn't give me that high-level overview that I'm looking for. In our installation, all of the seven databases are hosted on the same machine and same instance of SQL Server.

My initial misconception about transaction marking was that I assumed TFS itself would routinely insert marks into the transaction logs of the seven databases during its normal operation, at points in time where it knows that their state is consistent. This doesn't appear to be the case. The explanation of the BizTalk stored procedure indicates that the log mark insertions are initiated by SQL server as a DBA task. It seems that the insertion of these named marks is done just prior to performing database log backups. This makes sense, so presumably once SQL Server is told to make the marks, it waits for a point in time at which all transactions are closed across all related databases before inserting the marks simultaneously.

If I've got that overview correct, then my real problem is that I can't find an example of the T-SQL that would actually do this. In pseudocode I'd expect it to be something like...

begin distributed transaction across (list of 7 databases), timeout 300 seconds

insert transaction log mark 'safe to restore to this point'
end distributed transaction

I suspect that this page will probably end up having all the information on it I need, but after reading it several times I can't quite nail it. If anyone can help then I'd be most grateful.

James