Back Up Team Foundation Server

You can help prevent the loss of data and productivity by consistently backing up data for Visual Studio Team Foundation Server by using the tools that SQL Server provides. 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 routinely mark related transactions in every database that Team Foundation uses, you establish a series of common recovery points in those databases. If you regularly back up those databases, you reduce the risk of losing productivity or data because of equipment failure or other unexpected events. If your deployment uses SharePoint Products or is integrated with Microsoft Project Server, you must perform additional steps to back up and restore its databases, as detailed in the procedures in this topic.

If your deployment uses SQL Server Reporting Services, you must back up not only the databases but also the encryption key. For more information, see Back Up the Reporting Services Encryption Key.

The procedures in this topic explain how to create maintenance plans that perform either a full or an incremental backup of the databases and how to create tables and stored procedures for marked transactions. For maximum data protection, you should schedule full backups to run daily or weekly and incremental backups to run hourly. You can also back up of the transaction logs. For more information, see the following page on the Microsoft website: Creating Transaction Log Backups.

By following the procedures in this topic, you can back up not only the databases that are specific to Team Foundation but also all databases that your deployment of Team Foundation Server uses. For information about how to manually back up and restore individual databases, see the following pages on the Microsoft website: Backing Up and Restoring Databases in SQL Server and Using Marked Transactions. For information about how to use the Query Editor to create tables and stored procedures for marked transactions, see the following page on the Microsoft website: Editing SQLCMD Scripts with Query Editor.

If your deployment includes team project portals, you must also back up the databases that SharePoint Products uses according to the best practices for the version of that product in your deployment. If your deployment is integrated with Microsoft Project Server, you must also back up the databases that Project Server uses according to the best practices for the version of that product in your deployment.

In this topic:

1. Required Permissions

2. Identify Databases

3. Create Tables in Databases

4. Create a Stored Procedure for Marking Tables

5. Create a Stored Procedure for Marking All Tables At Once

6. Create a Stored Procedure to Automatically Mark Tables

7. Create a Scheduled Job to Run the Table-Marking Procedure

8. Create a Maintenance Plan For Full Backups

9. Create a Maintenance Plan For Differential Backups

10. Create a Maintenance Plan For Transaction Backups

11. Back Up Additional Lab Management Components

Required Permissions

To perform this procedure, you must be a member of both of the following groups:

• The Administrators security group on the server that is running the administration console for Team Foundation.

• The SQL Server System Administrator security group or your SQL Server Perform Back Up and Create Maintenance Plan permissions must be set to Allow on each instance of SQL Server that hosts the databases that you want to back up. 

Identify Databases

You can use the following table to identify databases in your deployment of Team Foundation Server. Not all deployments have all these databases. For example, if you did not configure Team Foundation Server with Reporting Services, you will not have the ReportServer or ReportServerTempDB databases. Similarly, you will not have the database for System Center Virtual Machine Manager (SCVMM), which is named VirtualManagerDB, if you did not configure Team Foundation Server to support Lab Management. In addition, the databases that Team Foundation Server uses might be distributed across more than one instance of SQL Server or across more than one server.

Database	Description
TFS_Configuration	The configuration database for Team Foundation Server contains the catalog, server names, and configuration data for the deployment. The name of this database might include additional characters between TFS_ and Configuration, such as the user name of the person who installed Team Foundation Server. For example, the name of the database might be TFS_UserNameConfiguration.  Important If you do not use marked transactions to guarantee common recovery points for all databases that Team Foundation uses, you must make sure to back up this database last and restore it before all other databases. Otherwise, you might not be able to restore the deployment.
TFS_Warehouse	The warehouse database contains the data for building the warehouse that Reporting Services uses. The name of this database might include additional characters between TFS_ and Warehouse, such as the user name of the person who installed Team Foundation Server. For example, the name of the database might be TFS_UserNameWarehouse.
TFS_CollectionName	The database for a team project collection contains all data for the team projects in that collection. This data includes source code, build configurations, and lab-management configurations. The number of collection databases will equal the number of collections. For example, if you have three collections in your deployment, you must back up three collection databases. The name of each database might include additional characters between TFS_ and CollectionName, such as the user name of the person who created the collection. For example, the name of a collection database might be TFS_UserNameCollectionName.
TFS_Analysis	The database for SQL Server Analysis Services contains the data sources and cubes for your deployment of Team Foundation Server. The name of this database might include additional characters between TFS_ and Analysis, such as the user name of the person who installed Analysis Services. For example, the name of the database might be TFS_UserNameAnalysis. Note You can back up this database, but you must rebuild the warehouse from the restored TFS_Warehouse database.
ReportServer	The database for Reporting Services contains the reports and report settings for your deployment of Team Foundation Server. Note If Reporting Services is installed on a separate server from Team Foundation Server, this database might not be present on the data-tier server for Team Foundation. In that case, you must configure, back up, and restore it separately from Team Foundation Server. However, you should synchronize the maintenance of the databases to avoid synchronization errors.
ReportServerTempDB	The temporary database for Reporting Services temporarily stores information when you run specific reports. Note If Reporting Services is installed on a separate server from Team Foundation Server, this database might not be present on the data-tier server for Team Foundation. In that case, you must configure, back up, and restore it separately from Team Foundation Server. However, you should synchronize the maintenance of the databases to avoid synchronization errors.
WSS_Config	The configuration database for SharePoint Products contains a list of all sites, such as content databases, site templates, custom web parts, and other settings from SharePoint Central Administration. Note You should not use marked transactions when you back up or restore the databases that SharePoint Products uses, but you should attempt to synchronize your backup and restoration schedules for SharePoint Products and Team Foundation Server to help avoid synchronization errors. For more information, see Backup and Recovery (SharePoint Server 2010), Protecting and restoring a farm (Office SharePoint Server 2007), or Protecting and restoring a farm (Windows SharePoint Services 3.0).
WSS_Content	The content database for SharePoint Products contains the actual content in team project portals. Note The name of this database will vary based on the version of SharePoint Products that is installed and whether the person who installed it customized the name. You should not use marked transactions when you back up or restore the databases that SharePoint Products uses, but you should attempt to synchronize your backup and restoration schedules for SharePoint Products and Team Foundation Server to help avoid synchronization errors. For more information, see Backup and Recovery (SharePoint Server 2010), Protecting and restoring a farm (Office SharePoint Server 2007), or Protecting and restoring a farm (Windows SharePoint Services 3.0).
WSS_AdminContent	The administration database for SharePoint Products contains the security information for users, roles, and databases. Note You should not use marked transactions when you back up or restore the databases that SharePoint Products uses, but you should attempt to synchronize your backup and restoration schedules for SharePoint Products and Team Foundation Server to help avoid synchronization errors. For more information, see Backup and Recovery (SharePoint Server 2010), Protecting and restoring a farm (Office SharePoint Server 2007), or Protecting and restoring a farm (Windows SharePoint Services 3.0).
VirtualManagerDB	The administration database for SCVMM contains the information that you view in the SCVMM Administrator Console, such as virtual machines, virtual machine hosts, virtual machine library servers, and their properties. Note If SCVMM is installed on a separate server from Team Foundation Server, this database might not be present on the data-tier server for Team Foundation. In that case, you must configure, back up, and restore it separately from Team Foundation Server. However, you should use marked transactions and synchronize the maintenance of the databases to avoid synchronization errors.

Create Tables in Databases

To make sure that all databases are restored to the same point, you can create a table in each database to mark transactions. You can use the Query function in SQL Server Management Studio to create an appropriate table in each database.

To create tables to mark related transactions in databases that Team Foundation uses

1. Log on to the server that is running the instance of SQL Server that contains the databases that you want to mark.

2. Open SQL Server Management Studio, and connect to the database engine.

3. In SQL Server Management Studio, right-click the name of the server, and then click New Query.

   The Database Engine Query Editor window opens.

4. On the Query menu, click SQLCMD Mode.

   The Query Editor executes sqlcmd statements in the context of the Query Editor. If the Query menu does not appear, click anywhere in the new query in the Database Engine Query Editor window.

5. On the SQL Editor toolbar, open the Available Databases list, and then click TFS_Configuration.

   Note

   TFS_Configuration is the default name of the configuration database. This name is customizable and might vary.

6. In the query window, type the following script to create a table in the configuration database:

   Use TFS_Configuration
   Create Table Tbl_TransactionLogMark
   (
   logmark int
   )
   GO
   Insert into Tbl_TransactionLogMark (logmark) Values (1)
   GO
   

7. Press F5 to run the script.

   If the script is well-formed, the message "(1 row(s) affected.)" appears in the Query Editor.

8. (Optional) Save the script.

9. Repeat steps 4−7 for every database in your deployment of Team Foundation Server, except for those used by SharePoint Products. If your deployment uses reporting or SharePoint web applications, make sure that you create tables in those databases also.

   Note

   If your deployment uses multiple instances of SQL Server to host databases for Team Foundation Server, you must log on to each instance to create the tables in the databases that are hosted on that instance.

Create a Stored Procedure for Marking Tables

After the tables have been created in each database that you want to back up, you must create a procedure for marking the tables.

To create a stored procedure to mark transactions in each database that Team Foundation Server uses

1. Log on to the server that is running the instance of SQL Server that contains the databases that you want to mark.

2. In SQL Server Management Studio, open a query window, and make sure that SQLCMD Mode is turned on.

3. On the SQL Editor toolbar, open the Available Databases list, and then click TFS_Configuration.

4. In the query window, type the following script to create a stored procedure to mark transactions in the configuration database:

   Create PROCEDURE sp_SetTransactionLogMark
   @name nvarchar (128)
   AS
   BEGIN TRANSACTION @name WITH MARK
   UPDATE TFS_Configuration.dbo.Tbl_TransactionLogMark SET logmark = 1
   COMMIT TRANSACTION
   GO
   

5. Press F5 to run the procedure.

   If the procedure is well-formed, the message "Command(s) completed successfully." appears in the Query Editor.

6. (Optional) Save the procedure.

7. Repeat steps 2−5 for every database in your deployment of Team Foundation Server. If your deployment uses reporting, make sure that you create stored procedures for those databases also. Do not perform these steps for any databases that SharePoint Products uses.

   Note

   If your deployment uses multiple instances of SQL Server to host databases that Team Foundation Server uses, you must log on to each instance to create a stored procedure for each databases that is hosted on that instance.

Create a Stored Procedure for Marking All Tables At Once

To make sure that all databases are marked, you can create a procedure that will run all the procedures that you just created for marking the tables. Unlike the previous procedures, this procedure runs only in the configuration database.

To create a stored procedure that will run all stored procedures for marking tables

1. Log on to the server that is running the instance of SQL Server that contains the configuration database for Team Foundation.

2. In SQL Server Management Studio, open a query window, and make sure that SQLCMD Mode is turned on.

3. On the SQL Editor toolbar, open the Available Databases list, and then click TFS_Configuration.

4. In the query window, type the following script to create a stored procedure that executes the stored procedures that you created in each database that Team Foundation Server uses. Replace ServerName with the name of the server that is running SQL Server, and replace TFS_CollectionName with the name of the database for each team project collection. If you are using more than one server or instance of SQL Server, replace ServerName with ServerName\InstanceName.

   CREATE PROCEDURE sp_SetTransactionLogMarkAll
   @name nvarchar (128)
   AS
   BEGIN TRANSACTION
   EXEC [ServerName].TFS_Configuration.dbo.sp_SetTransactionLogMark @name
   EXEC [ServerName].ReportServer.dbo.sp_SetTransactionLogMark @name
   EXEC [ServerName].ReportServerTempDB.dbo.sp_SetTransactionLogMark @name
   EXEC [ServerName].TFS_CollectionName1.dbo.sp_SetTransactionLogMark @name
   EXEC [ServerName].TFS_CollectionName2.dbo.sp_SetTransactionLogMark @name
   EXEC [ServerName].TFS_CollectionName3.dbo.sp_SetTransactionLogMark @name
   EXEC [ServerName].TFS_CollectionName4.dbo.sp_SetTransactionLogMark @name
   EXEC [ServerName].TFS_Warehouse.dbo.sp_SetTransactionLogMark @name
   EXEC [ServerName].VirtualManagerDB.dbo.sp_SetTransactionLogMark @name
   COMMIT TRANSACTION
   GO
   

   Note

   If your deployment does not use reporting, you do not need to include lines for the reporting databases. If your deployment does not use Visual Studio Lab Management, you do not need to include lines for the databases that it uses.

5. Press F5 to run the procedure.

   Note

   If you have not restarted SQL Server Management Studio since you created the stored procedures for marking transactions, one or more red wavy lines might underscore the name of the server and the names of the databases. However, the procedure should still run.

   If the procedure is well-formed, the message "Command(s) completed successfully." appears in the Query Editor.

6. (Optional) Save the procedure.

Create a Stored Procedure to Automatically Mark Tables

When you have a procedure that will run all stored procedures for table marking, you must create a procedure that will mark all tables with the same transaction marker. You will use this marker to restore all databases to the same point.

To create a stored procedure to mark the tables in each database that Team Foundation Server uses

1. Log on to the server that is running the instance of SQL Server that contains the databases that you want to mark.

2. In SQL Server Management Studio, open a query window, and make sure that SQLCMD Mode is turned on.

3. On the SQL Editor toolbar, open the Available Databases list, and then click TFS_Configuration.

4. In the query window, type the following script to mark the tables with 'TFSMark':

   EXEC sp_SetTransactionLogMarkAll 'TFSMark'
   GO
   

   Note

   TFSMark is an example of a mark. You can use any sequence of supported letters and numbers in your mark. If you have more than one marked table in the databases, record which mark you will use to restore the databases. For more information, see the following page on the Microsoft website: Using Marked Transactions.

5. Press F5 to run the procedure.

   If the procedure is well-formed, the message "(1 row(s) affected)" appears in the Query Editor. The WITH MARK option applies only to the first "BEGIN TRAN WITH MARK" statement for each table that has been marked.

6. Save the procedure.

Create a Scheduled Job to Run the Table-Marking Procedure

Now that you have created and stored all the procedures that you will need, you must schedule the table-marking procedure to run just before the scheduled backups of the databases. You should schedule this job to run approximately one minute before the maintenance plan for the databases runs.

To create a scheduled job for table marking in SQL Server Management Studio

1. Log on to the server that is running the instance of SQL Server that contains the databases that you want to mark.

2. In Object Explorer, expand SQL Server Agent, right-click Jobs, and then click New Job.

   The New Job window opens.

3. In Name, type a name for the job.

4. (Optional) In Description, type a description of the job.

5. In Select a page, click Steps.

6. In Steps, click New.

   The New Job Step window opens.

7. In Step Name, type a name for the step.

8. In Database, click the name of the configuration database (by default, TFS_Configuration), and then click Open.

9. Browse to the procedure that you created for marking the tables, click Open two times, and then click OK.

   Note

   The procedure that you created for marking the tables runs the following step:

   EXEC sp_SetTransactionLogMarkAll 'TFSMark'
   

10. In Select a page, click Schedules, and then click New.

    The New Job Schedule window opens.

11. In Name, type a name for the schedule.

12. In Frequency, change the frequency to match the plan that you created for backing up the databases.

13. In Daily Frequency, change the occurrence so that the job is scheduled to run one minute before the backup for the databases, and then click OK.

14. In New Job, click OK to finish creating the scheduled job.

Create a Maintenance Plan For Full Backups

After you create a scheduled job for marking the databases, you can use the Maintenance Plan Wizard to schedule full backups of all databases that your deployment of Team Foundation Server uses.

To create a maintenance plan for full backups

1. Log on to the server that is running the instance of SQL Server that contains the databases that you want to back up.

2. Click Start, click All Programs, click Microsoft SQL Server 2008, and then click SQL Server Management Studio.

   1. In the Server type list, click Database Engine.

   2. In the Server name and Authentication lists, click the appropriate server and authentication scheme.

   3. If your instance of SQL Server requires it, in User name and Password, type the credentials of an appropriate account.

   4. Click Connect.

3. In SQL Server Management Studio, expand the Management node, right-click Maintenance Plans, and then click Maintenance Plan Wizard.

4. On the welcome page for the SQL Server Maintenance Plan Wizard, click Next.

   The Select Plan Properties page appears.

5. In the Name box, type a name for the maintenance plan.

   For example, you could name a plan for full backups TfsFullDataBackup.

6. Click Single schedule for the entire plan or no schedule, and then click Change.

7. Under Frequency and Daily Frequency, specify options for your plan.

   Under Duration, leave the default value, No end date.

8. Click OK, and then click Next.

9. On the Select Maintenance Tasks page, select the Backup Database (Full), Execute SQL Server Agent Job, and Back up Database (Transaction Log) check boxes, and then click Next.

10. On the Select Maintenance Task Order page, change the order so that the full backup runs first, then the Agent job, and then the transaction log backup.

    Click Next

    For more information about this dialog box, press F1. Also, search for Maintenance Plan Wizard on the following page of the Microsoft website: SQL Server Books Online.

11. On the Define Back Up Database (Full) Task page, click the down arrow, click All Databases, and then click OK.

12. Specify the backup options for saving the files to disk or tape, as appropriate for your deployment and resources, and then click Next.

13. On the Define Execute SQL Server Agent Job Task page, select the check box for the scheduled job that you created for table marking, and then click Next.

14. On the Define Back Up Database (Transaction Log) Task page, click the down arrow, click All Databases, and then click OK.

15. Specify the backup options for saving the files to disk or tape as appropriate for your deployment and resources, and then click Next.

16. On the Select Report Options page, specify report distribution options, and then click Next two times.

17. On the Complete the Wizard page, click Finish.

    SQL Server creates the maintenance plan and backs up the databases that you specified based on the frequency that you specified.

18. Click Close.

Create a Maintenance Plan For Differential Backups

You can use the Maintenance Plan Wizard to schedule differential backups for all databases that your deployment of Team Foundation Server uses.

To create a maintenance plan for differential backups

1. Log on to the server that is running the instance of SQL Server that contains the databases that you want to back up.

2. Click Start, click All Programs, click Microsoft SQL Server 2008, and then click SQL Server Management Studio.

   1. In the Server type list, click Database Engine.

   2. In the Server name and Authentication lists, click the appropriate server and authentication scheme.

   3. If your instance of SQL Server requires it, in User name and Password, type the credentials of an appropriate account.

   4. Click Connect.

3. In SQL Server Management Studio, expand the Management node, right-click Maintenance Plans, and then click Maintenance Plan Wizard.

4. On the welcome page for the SQL Server Maintenance Plan Wizard, click Next.

5. On the Select Plan Properties page, in the Name box, type a name for the maintenance plan.

   For example, you could name a plan for differential backups TfsDifferentialBackup.

6. Click Single schedule for the entire plan or no schedule, and then click Change.

7. Under Frequency and Daily Frequency, specify options for your backup plan.

   Under Duration, leave the default value, No end date.

8. Click OK, and then click Next.

9. On the Select Maintenance Tasks page, select the Back up Database (Differential) check box, and then click Next.

10. On the Define Back Up Database (Differential) Task page, click the down arrow, click All Databases, and then click OK.

11. Specify the backup options for saving the files to disk or tape as appropriate for your deployment and resources, and then click Next.

12. On the Select Report Options page, specify report distribution options, and then click Next two times.

13. On the Complete the Wizard page, click Finish.

    SQL Server creates the maintenance plan and backs up the databases that you specified based on the frequency that you specified.

14. Click Close.

Create a Maintenance Plan For Transaction Logs

You can use the Maintenance Plan Wizard to schedule transaction log backups for all databases that your deployment of Team Foundation Server uses.

To create a maintenance plan for transaction log backups

1. Log on to the server that is running the instance of SQL Server that contains the databases that you want to back up.

2. Click Start, click All Programs, click Microsoft SQL Server 2008, and then click SQL Server Management Studio.

3. In the Server type list, click Database Engine.

   1. In the Server name and Authentication lists, click the appropriate server and authentication scheme.

   2. If your instance of SQL Server requires it, in User name and Password, type the credentials of an appropriate account.

   3. Click Connect.

4. In SQL Server Management Studio, expand the Management node, right-click Maintenance Plans, and then click Maintenance Plan Wizard.

5. On the welcome page for the SQL Server Maintenance Plan Wizard, click Next.

   The Select Plan Properties page appears.

6. In the Name box, type a name for the maintenance plan.

   For example, you could name a plan to back up transaction logs TfsTransactionLogBackup.

7. Click Single schedule for the entire plan or no schedule, and then click Change.

8. Under Frequency and Daily Frequency, specify options for your plan.

   Under Duration, leave the default value, No end date.

9. Click OK, and then click Next.

10. On the Select Maintenance Tasks page, select the Execute SQL Server Agent Job and Back up Database (Transaction Log) check boxes, and then click Next.

11. On the Select Maintenance Task Order page, change the order so that the Agent job runs before the transaction-log backup, and then click Next.

    For more information about this dialog box, press F1. Also, search for Maintenance Plan Wizard on the following page of the Microsoft website: SQL Server Books Online and.

12. On the Define Execute SQL Server Agent Job Task page, select the check box for the scheduled job that you created for table marking, and then click Next.

13. On the Define Back Up Database (Transaction Log) Task page, click the down arrow, click All Databases, and then click OK.

14. Specify the backup options for saving the files to disk or tape as appropriate for your deployment and resources, and then click Next.

15. On the Select Report Options page, specify report distribution options, and then click Next two times.

16. On the Complete the Wizard page, click Finish.

    SQL Server creates the maintenance plan and backs up the transaction logs for the databases that you specified based on the frequency that you specified.

17. Click Close.

Back Up Additional Lab Management Components

If you use Visual Studio Lab Management in your deployment of Team Foundation Server, you must also back up each machine and component that Lab Management uses. The hosts for the virtual machines and the SCVMM library servers are separate physical computers that are not backed up by default. You must specifically include them when you plan your backup and restoration strategies. The following table summarizes what you should back up whenever you back up Team Foundation Server.

The following table contains tasks and links to procedural or conceptual information about how to back up the additional machines for an installation of Lab Management. You must perform the tasks in the order shown, without skipping any tasks.

To back up the machines that are running any SCVMM components, you must be a member of the Backup Operators group on each machine.

See Also

Tasks

Restore Data to the Same Location

Back Up the Reporting Services Encryption Key

Restore Data to a Different Server or Instance

Other Resources

Managing Data

Managing Team Foundation Server Data-Tier Servers

Managing Team Foundation Server