Share →
Buffer
Dan Wood (Mugshot)

I have encountered a great deal of companies that are at risk when it comes to backing up their TFS databases. Some just turn it over to the DBA team without any conversation about data loss risk or the periodicity of backups. Many of these customers put their databases in Simple recovery mode and perform weekly full backups. Others do the full backups daily, but still do not manage the transaction logs. Their assumptions are that if disaster strikes they will be able to reinstall TFS, restore the last backup of their databases and then TFS will magically start working again.

Their assumptions are wrong.

TFS utilizes multiple databases to store data for source control, work item tracking, server configuration and reporting. In order for TFS to perform correctly, these databases must be kept in a synchronized state. This creates challenges for traditional disaster prevention strategies in regards to database backups. Since a backup of the collection database could be occurring while modifications are being made to the configuration database and vice versa. The time offset of the two backups could be significant either by design due to the backup plan or because the collection database is large. My current customer has a collection database that is over four terabytes (which is ridiculous by the way, since about half of the database is made up of old test attachments, but that is a subject for a different blog). It takes them about five hours to perform a full backup and before I arrived that was the only kind of backup they performed.

To address the need to ensure, at a minimum, that the collection and configuration databases are kept synchronized, transaction log marks are employed. That way, if disaster strikes the databases can be restored to the same consistent point. My customer at least understood that the databases were supposed to be kept synchronized. Their answer was to take TFS offline once a week and perform the full backup which caused several hours of downtime every week. Just to be clear, the databases do not have to be taken offline or access restricted to users during the backup process. There is a better way (obviously). In fact there are a couple of better ways.

The first alternative is to use the TFS scheduled backup utility that as of TFS 2012 Update 2, is included in the base product. Previously, it was part of the TFS Power Tools. The built-in utility makes the process of creating backups of all the TFS related databases easy. The restore process is also straightforward when the tool is used to complete the backups.

image

TFS Scheduled Backup Tool

However, the simplicity does not come without disadvantages:

1. There is no option for configuring alternative security credentials for accessing the backup folder. The TFS service account must be used.

2. There is no option for specifying different backup retention policies for the different backup types.

3. There is very little logging associated with the utility. Notification is limited to TFS administrators.

4. The data team has no visibility in the progress of the job. It is managed completely with the TFS Admin console.

5. Data recovery is limited to the periodicity of the transaction log backup. This is mostly trivial, since the log backup periodicity should be less than the acceptable data loss period. But having multiple log marks in a single log backup is not supported with the tool since the log marks are all named the same and performed in conjunction with the log backup.

The full documentation on how to configure the TFS backup process can be found at: http://msdn.microsoft.com/en-us/library/vstudio/ms253070(v=vs.120).aspx. It is well documented as long as you are using the built in tool. However, if you manually backup the databases, the tool will not work for the restore process. My colleague Martin Hinshelwood would tell you that is why you should just use the tool and he is probably right, but my background as a DBA cringes at the loss of control and visibility the tool costs for the gain of simplicity. That being said, if you are a small development shop and you are managing your own database deployment, by all means, just use the tool. Larger organizations and those with more structure and controls built around the database environment will most likely prefer to manage the TFS related databases the same way they manage all other database resources. The important issue is that the databases all get backed up and the interdependent TFS databases are synchronized.

Since the backup process is already covered in detail, I will just show an implementation that I have used a few times that works well.

Backup Plan Overview

For our discussion, the TFS backup plan is implemented by four distinct SQL Server Agent Jobs which also handle the backup file cleanup process:

1. TFS Weekly Full Backup 

Weekly full backup of all TFS associated relational databases (excluding SharePoint, which for the sake of this discussion is assumed to be managed separately):

a. Tfs_Configuration
b. Tfs_Collection
c. Tfs_Warehouse
d. ReportServer
e. ReportServerTempDB

2. TFS Daily Analysis Database Backup

Daily backup of the TFS Analysis Services database:

a. Tfs_Analysis

Note: Backing up the Analysis database is completely optional since it is rebuilt every night anyway. However, in most cases a restore will take significantly less time than a rebuild and will allow for instance access to TFS reporting. If TFS reporting is a high-value component for your organization, I would highly recommend including the Analysis database backup.

3. “TFS Daily Differential Backup” Daily differential backup of TFS specific relational databases:

a. Tfs_Configuration
b. Tfs_Collection
c. Tfs_Warehouse
d. ReportServer*
e. ReportServerTempDB*

4. “TFS Hourly Transaction Log Backup“ Hourly transaction log backup of TFS specific relational databases:

a. Tfs_Configuration
b. Tfs_Collection
c. Tfs_Warehouse
d. ReportServer*
e. ReportServerTempDB*

Additionally, the encryption key for reporting services also needs to be backed up. This can be done from the report server with either the Reporting Services Configuration utility or with the RSKEYMGMT command line tool. This key is required to attach reporting services to a restored ReportServer database and maintain the data source credentials. However, if the key is unavailable or lost, reporting services can still be configured, but the security credentials for any data sources (TFS just uses two out of the box) will need to be deleted and re-entered. 

 *The reporting databases do not technically require synchronization with the TFS databases. Depending on their volatility, their backup periodicity could be longer with perhaps only a weekly full backup and the databases placed in Simple recovery mode.

For the purpose of explanation we will use the following backup schedule:

Backup Type

Periodicity

Schedule

Full

Weekly

02:00 Sundays

Analysis

Daily

00:00

Differential

Daily

02:00 Monday – Saturday

Transaction Log

Hourly

Hourly

Backup file retention is managed by the backup jobs.

Backup Type

Retention

Full

3 Weeks

Analysis

2 Days

Differential

2 Weeks

Transaction Log

2 Weeks

Backup Plan Details

The SQL Agent jobs that implement the backup plan are broken down in to multiple steps as follows:

1.  “TFS Weekly Full Backup

a. Execute full Backup of all databases (Transact-SQL Script)

 b. Delete all full backup files older than 2 weeks (PowerShell)

 2. “TFS Daily Analysis Database Backup”

a. Execute backup of Tfs_Analysis OLAP database. Overwrite existing backup file (SQL Server Analysis Services Command) 

 3. “TFS Daily Differential Backup”

a. Execute differential Backup of all TFS specific databases (Transact-SQL Script) 

 b. Delete all differential backup files older than 2 weeks (PowerShell)

 
4. “TFS Hourly Transaction Log Backup“

a. Execute the stored procedure msdb.dbo.usp_TransactionLogMarkAll. (Transact-SQL Script)

The stored procedure ensures the existence of the database artifacts required to support a synchronized restore of the TFS databases by creating a “Save Mark” in all the database’s transaction logs to support an error free restoration of TFS (reporting databases could be excluded). If a new team project collection is added to TFS, the stored procedure will automatically add the required artifacts to the new database as long as the database is named with the default “TFS_” prefix.

Here is the script to create the stored procedure: 

 msdb.dbo.usp_TransactionLogMarkAll

b. Execute transaction log backup of all specific databases (Transact-SQL Script)

 c. Delete all transaction log backups older than 2 weeks (PowerShell)

 d. Record the log mark information in a file in the backup folder (SQLCMD) 

 This step may seem superfluous. Since all the log marks are named the same, why should we care? The answer is simple; I like documentation. Besides if you lose the entire SQL Server, or if someone drops a database without preserving the backup history, the MSDB database will have no record of log marks and when they were performed.

e. Cleanup old log mark data for deleted transaction logs 

 The key to keeping the databases synchronized is executing the log mark stored procedure immediately before running the transaction log back up job.

I generally do not schedule the stored procedure to be run before the Full and Differential backups, but a good case could certainly be made for doing so.

Database Restore Steps

The steps to restore the database are as follows:

1. Restore the most recent full backup of the databases. I have shown just the Configuration database as an example, but the steps would obviously need to be done for all the remaining databases. This restore is completed with the “NORECOVERY” option to facilitate the restoration of the differential and transaction log backups. If the files are not being restored to the same physical path that they originally resided on, the “MOVE” option must be specified. I add the “STATS = 10” option so that I can monitor the restore progress. 

 2. Restore the most recent differential backup with the “NORECOVERY” option. If no differential is available due to recovery being required prior to a differential backup, skip this step. 

 3. Sequentially restore all transaction log backups that were performed since the last differential backup, or the last full backup if no differential backup is available. Restore the last transaction log in the sequence with the “RECOVERY” and “STOPATMARK” options to ensure that all databases are restored to the same point “transactionally” and not necessarily in time.

  4. Repeat these steps for all the specified databases.

If for some reason it is desired to restore the databases to an earlier point than the latest transaction log, the RECOVERY and STOPATMARK options can be specified earlier in the chain. Be aware however, that once a database has been “Recovered” no additional backups can be applied. If the database is prematurely recovered, the restore sequence must be restarted at the beginning. If you choose to execute the log mark stored procedure before every Full and Differential backup, the STOPATMARK option can be used with those recover options as well.

As a final unsupported option, in the case where no log marks are available for whatever reason the STOPAT option can be used as a last resort (STOPAT = ‘Dec 25, 2013 12:00 AM’). The reason this method is unsupported (since it is a much simpler approach) is that the potential exists for dependent transactions to be in flight on more than one TFS database at the precise moment of the STOPAT and restoring to that point could possibly result in the inability to use the restored databases. I personally believe that risk is rather small, but it is a risk so Microsoft does not support its use. Caveat Emptor!

Restoring the Analysis Services Database

The easiest way to restore the analysis database is using SQL Server Management Studio although XMLA commands can be used for restore automation: 

 To restore the database with SQL Server Management Studio (SSMS), perform the following steps:

1. Right click on the Databases node of the Analysis Server and choose Restore.

image 
Restoring the Analysis Service Database

2. Specify the backup file. By default it is named database_name.abf, for TFS this is “Tfs_Analysis.abf”

3. Select the database from the “Restore database” drop down and specify a location to store the database. If the database already exists it can be overwritten. If the backup file was encrypted during the backup process (the example I used above does not encrypt the backup), specify the password and click OK.

image
Restoring the Analysis Service Database

The databases are now all restored and ready for TFS. 

Restoring TFS

Since almost all of the information that is used to configure TFS is stored in the TFS databases, there really isn’t a “Restore TFS” step. Instead there is a re-install and/or reconfigure TFS.
 
If the data tier was the only part of the environment that needed to be restored from backup then you may need to reconfigure TFS to look to the restored databases. This depends on whether or not new hardware is involved. If the databases will be restored to the same intact SQL Server, then all that is needed is to stop the services on the Application tier: 

 Restore the databases and the start the TFS services back up   

  That’s all there is to it, you’re done. 

If the databases are to be restored to different hardware then TFS will need to  be reconfigured using the TFSConfig RemapDBs and TFSConfig RegisterDB commands.  Instructions for this operation can be found here: http://msdn.microsoft.com/en-us/library/hh529826.aspx
 
If it was just the application tier that was affected, simply reinstall TFS on a new or existing machine and run the Application Tier Only wizard.
 
The important thing to remember is that TFS, like every other database linked application requires a good back strategy. TFS adds a bit more complexity since the multiple databases it uses must be able to be restored to the same point transactional-wise and not necessarily the same point in time.
 
 
 
 
 
 
 
 
Print Friendly
  • Hi Dan,

    The formatting in this article is messed up. I see span style tags everywhere throughout. Tried several versions of Chrome and IE.

  • DanLWood

    Thanks Joshua! Should be all fixed now!

  • Question: Microsoft has gone to a fairly aggressive release cadence for Team Foundation Server updates. How can we be sure that Microsoft won’t update our databases in a way that will remove our database changes added to make the backup work?

  • DanLWood

    Great question Steve. Unfortunately the answer is that we can’t. I would be surprised if Microsoft bothered to remove custom store procedures that I added for my backup strategy, but I wouldn’t be shocked. The bottom line is that with adequate alerts set up to inform the administrator of a backup failure, we will at least be informed that the procedure was removed and the backups failed.

  • Angela Dugan

    I find myself butting heads with DBAs all the time about how best to backup TFS databases. 9 times out of 10 they are doing exactly what you describe way at the beginning, and I try to explain why they put the development org at risk by doing that, but I never felt like I was describing the situation well from a DBA’s point of view (I’m not a DBA). Thank you for laying this out in such a detailed manner, and in a way that DBAs can better relate to. Definitely bookmarking this to share with the next DBA I have this conversation with 🙂

  • You are most welcome Angela. If you need any more information or some backup, let me know. I have many years of experience working with SQL Server and DBA’s (I was one), so I know the battles all too well.

  • Aminur Rashid

    while running schedule backup, will TFS down or what will be the impact on TFS?

  • Aminur, there should be no significant impact to TFS during the backups and TFS is certainly not down. Depending on the hardware running SQL Server, you may see some performance impact during the differential and full backups. The log backups should be transparent as long as they are done frequently. The bottom line is that as long as the database is being cared for properly and the hardware is not under-powered, backups should be all but imperceptible.

  • Aminur Rashid

    Thanks

  • Dianna

    Thanks for providing clarity for DBAs as we had that same issue but after I raised the point about restore not working with the DBA method, they told us we are now responsible for our own backups 🙁 We are a large organization with many collections (one per business unit). We have 2 use cases, Disaster REcovery backups for total loss and Individual Collection backups to restore to test servers. We maintain several TFS instances for testing in addition to production and frequently need to restore a recent production backup for a single Collection to it for testing customizations and integrations. For the latter case, if we use manual backup (detach/backup/reattach) from production and then restore it to the test server all is good. However we if we use the bak files from the TFS schedule backups mechanism, we cannot reattach the Collection on our test server because it is not found. Is that expected? We would like to just use the TFS Scheduled backups for both Disaster Recovery and individual COllection restores. Any advice would be appreciated

  • Dianna, Unfortunately, the behavior you are seeing is as designed. Since the collection databases are linked to the configuration database unless detached you can only restore the collections on a new server if you also restore the configuration database when using the disaster recovery method. To clone just a collection, you will indeed have to detach the collection first, back it up and then reattach as you described. Also remember to generate a new GUID for the collections when you are cloning them (TFSConfig ChangeServerID) to avoid VS client workspace confusion. Sorry to hear that your DBAs passed off on their responsibilities… I am not a fan of that decision. :-/

  • Dianna

    Thanks so much for taking the time to answer 🙂

  • Thomas Trotzki

    under “TFS Scheduled Backup Tool” Point 1 you wrote: “There is no option for configuring alternative security credentials for accessing the backup folder. The TFS service account must be used.”
    If you have a unconfigured TFS Installation (no configuration wizard run), the TFS service accounts is not specified at the point in time when the restore is done. So what account is used for the restore? It’s also not the account running the TFS admin console, we get “access denied” accessing the backup share while TFS restore, but not if accessing it via Windows Explorer.

  • Peter Hecht

    I’m wondering about detaching and attaching collections. I don’t see anything in your scripts above that detach the collection. Isn’t that going to make re-attching it to new software difficult?

  • Pradeep Nair

    Hi,
    First of thanks for this blog. Its a really eye-opener.
    Though blog is very old but the problem is still the same which we are facing.
    Just in case all that we have is the *.bak file from the SQL site, is there anyway to get the TFS restored from those files.
    Due to corrupt state of TFS 2013 it was uninstalled without realizing that the TFS was never been backed up using the tool.
    Any help or direction would be really helpful.
    Regards
    Pradeep