VSTS Pioneer TFS2010: SQL Backups

Grant Holliday’s blog

Senior Service Engineer, Microsoft Visual Studio Team Foundation Service

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

Location

Schedule

Retention

Databases

DatabaseBackup - Weekly FULL

E:\MSSQL\Backup\Full

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

Delete files older than 144 Hours (6 days)

SYSTEM_DATABASES, USER_DATABASES

DatabaseBackup - Nightly DIFF

E:\MSSQL\Backup\Diff

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

Delete files older than 23 hours

SYSTEM_DATABASES, USER_DATABASES

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

DatabaseBackup - 15min LOG

F:\MSSQL\Backup\Log

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

Delete files older than 24 hours

SYSTEM_DATABASES, USER_DATABASES

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. 

Compression

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.