backup of TFS with DPM 2007 - different backup times

Question

I have DPM set up to back up my TFS server every 30 minutes, the reason being it's a way better interface than the quirky SQL backup interface.

I also do a full backup nightly using an SQL maintenance job.

My thinking is I would use DPM to restore my databases in case of losing my database and the nightly full backup would be a "just in case" the DPM restore doesn't work.

I was thinking a little harder about this set up today and started to think about the fact that the DPM backup of the individual databases happens at different 30 min windows.. i.e. one happens at 13h30, another at 13h34 etc.

Would this difference in time be a problem when it comes to restoring the TFS server? If I restore the databases and they are from different times, will this create corruption with pointers in one database pointing to missing items in the other database.. do the databases even rely on each other or are they completely interdependant.

Lastly, how would SQL (log) backup cope with this?

Answer 1

As best I can tell, your concerns are well founded.

The TFS backup guide doco goes on and on about the need to ensure the TFS_ databases are backed up and restored as an atomic unit:

To successfully back up Team Foundation Server, you must not only back up all databases that the deployment uses, you must also synchronize the backups to the same point in time. You can manage this synchronization most effectively if you use marked transactions.

[...] If you back up and restore only one database, its data will be out of synchronization with the data in the other databases

As best I can tell, even a DPM backup across all the databases at the same time is not going to achieve this. It will achieve consistency on a per-database basis (via VSS), but not across databases, ie not at the transactional level. Database backups are, I believe, processed sequentially.

The best strategy seems to be to follow the proceedures in the backup guide (above) to setup a regular, SQL backup strategy that includes transaction log marking and ensure these backup files are backed up by DPM. In the event of a failure, restore the databases using DPM into 'recovery' mode, then complete the restore to the last consistent transaction mark across all databases (using SQL management tooling). But certainally being able to use DPM to skip much of the pain associated with re-setting up TFS / SharePoint in a DR scenario will save a lot of time.

Setting up a backup set in this way, including transaction marking, can be automated using the TFS PowerTools Team Foundation Backup add-in. In theory this can also be used for the restore, however there are some issues finding the transaction mark in DR scenarios.