VSTS Pioneer TFS2010: SQL Backups

Grant Holliday’s blog

Service Engineer, Microsoft Visual Studio Online - Team Foundation Server

VSTS Pioneer TFS2010: SQL Backups

  • Comments 0

(See this summary on the VSTS Pioneer dogfood server and all the other posts.)

Having an adequate backup strategy for your Visual Studio 2010 Team Foundation Server is a very important part of any deployment. I’ll share with you how we are running backups on our Pioneer Dogfood TFS2010 server, but you should refer to the MSDN guidance on How to: Back Up Team Foundation Server for definitive guidance.

To prevent accidental corruption of data, you must synchronize all backups of databases that Team Foundation Server uses, which include databases for SQL Server and SharePoint Products, to the same time stamp.

In the fine print a lot of people (myself included) don’t realize that you must restore all databases to the same point in time otherwise you may not have a functioning system. The stumbling block that we ran into on a recent upgrade dry-run was that the ‘Tfs_Configuration’ database was restored to a point in time that was 3 hours earlier than one of our collection databases.  This caused identity information in the collection database to be out of sync with the configuration database and caused the cloned system to be unusable.

The reason this happened is because it takes us ~3 hours to backup our collection database, so the timeline went something like this:

  • 12:00 Backup Tfs_Configuration (1 minute)
  • 12:01 Backup Tfs_VSTSDF collection (3 hours)

The additional step we were missing in our dry-run steps was to restore the transaction logs for all the databases to a common point in time (a time after the collection database had finished backing up). See Restoring a database to a point within a backup and Performing a Complete Database Restore (Full Recovery model).

Our backup process

SQL backups are performed using stored procedures from Ola Hallengren. See SQL Server 2005 and 2008 - Backup, Integrity Check and Index Optimization

Essentially a stored procedure is called with a number of parameters from a SQL Agent Job which performs the backups.

The reason these scripts are used instead of standard SQL Maintenance Plans, is because they give us a more flexible and robust backup solution.  For instance, the backup job log is written as the job executes & we have the flexibility to exclude certain databases while automatically including all new databases created on the server.

Job Name





DatabaseBackup - Weekly FULL


Occurs every week on Wednesday at 12:00:00 AM

Delete files older than 144 Hours (6 days)


DatabaseBackup - Nightly DIFF


Occurs every week on Monday, Wednesday, Thursday, Friday, Saturday, Sunday at 10:00:00 PM

Delete files older than 23 hours


* Automatically does a FULL backup if a DIFF cannot be performed.

DatabaseBackup - 15min LOG


Occurs every day every 15 minute(s) between 12:00:00 AM and 11:59:59 PM.

Delete files older than 24 hours


Then we have our IT group’s tape backup process come along every night and backup the SQL backup files to tape for on-site and off-site retention.

Backup Job Alerts

Email alerts are sent to a monitoring email list when each SQL backup job completes (regardless of success or failure).  This is done using the SQL Mail functionality. 


Additionally, if you are running SQL Server 2008 Enterprise Edition – you have SQL Backup Compression available to you. This can greatly reduce the size of your backups and the amount of I/O required, therefore decreasing the time it takes to perform backups & restores.  The tradeoff is of course, additional CPU usage.  See the following SQLCAT Tuning the Performance of Backup Compression in SQL Server 2008 article for more information on this.