Manually back up Team Foundation Server

You can manually back up data for Visual Studio Team Foundation Server by using the tools that SQL Server provides. However, you might need to configure backups manually if your deployment has security restrictions that prevent use of that tool. To manually 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 SQL Server Reporting Services, you must back up not only the databases but also the encryption key. For more information, see How to: 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.

In this topic:

• Required Permissions

• Identify Databases

• Create Tables in Databases

• Create a Stored Procedure for Marking Tables

• Create a Stored Procedure for Marking All Tables At Once

• Create a Stored Procedure to Automatically Mark Tables

• Create a Scheduled Job to Run the Table-Marking Procedure

• Create a Maintenance Plan For Full Backups

• Create a Maintenance Plan For Differential Backups

• Create a Maintenance Plan For Transaction Backups

• Back Up the Reporting Services Encryption Key

• Create a Back Up Plan for SharePoint Foundation

• Back Up Additional Lab Management Components

Required permissions

To perform this procedure, you must be a member of all 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. Alternatively, 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. 

• The Farm Administrators group in SharePoint Foundation, or an account with the permissions required to back up the farm.

Identify databases

Before you begin, you should take the time to identify all the databases you will need to back up if you would ever have to fully restore your deployment. This includes databases for SharePoint Foundation and SQL Server Reporting Services. These might be on the same server, or you might have databases distributed across multiple servers. For a complete table and description of TFS databases, including the default names for the databases, see Understand backing up Team Foundation Server.

To identify databases

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

2. In SQL Server Management Studio, in Object Explorer, expand the name of the server and then expand Databases.

3. Review the list of databases and identify those used by your deployment.

   For example, Fabrikam, Inc.'s TFS deployment is a single-server configuration, and it uses the following databases:

   • the configuration database (Tfs_Configuration)

   • the collection database (Tfs_DefaultCollection)

   • the database for the data warehouse (Tfs_Warehouse)

   • the reporting databases (ReportServer and ReportServerTempDB)

   • the databases used by SharePoint Foundation (WSS_AdminContent, WSS_Config, WSS_Content, and WSS_Logging)

     Important

     Unlike the other databases in the deployment, the databases used by SharePoint Foundation should not be manually backed up using the tools in SQL Server. Follow the separate procedure "Create a Back Up Plan for SharePoint Foundation " later in this topic for backing up these databases.

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. Open SQL Server Management Studio, and connect to the database engine.

2. In SQL Server Management Studio, highlight the name of the server, open the submenu, and then choose New Query.

   The Database Engine Query Editor window opens.

3. On the Query menu, choose SQLCMD Mode.

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

4. On the SQL Editor toolbar, open the Available Databases list, and then choose TFS_Configuration.

   Note

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

5. In the query window, enter 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
   

6. Choose the F5 key to run the script.

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

7. (Optional) Save the script.

8. Repeat steps 4−7 for every database in your deployment of TFS, except for those used by SharePoint Products. In the fictitious Fabrikam, Inc. deployment, you would repeat this process for all of the following databases:

   • Tfs_Warehouse

   • Tfs_DefaultCollection

   • ReportServer

   • ReportServerTempDB

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. In SQL Server Management Studio, open a query window, and make sure that SQLCMD Mode is turned on.

2. On the SQL Editor toolbar, open the Available Databases list, and then choose TFS_Configuration.

3. In the query window, enter 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
   

4. Choose the F5 key to run the procedure.

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

5. (Optional) Save the procedure.

6. Repeat steps 2−5 for every TFS database.  In the Fabrikam, Inc. deployment, you would repeat this process for all of the following databases:

   • Tfs_Warehouse

   • Tfs_DefaultCollection

   • ReportServer

   • ReportServerTempDB

   Tip

   Make sure that you select the name of the database you want to create the stored procedure for from the Available Database list in Object Explorer before you create the procedure. Otherwise when you run the script the command will display an error that the stored procedure was already exists.

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. In SQL Server Management Studio, open a query window, and make sure that SQLCMD Mode is turned on.

2. On the SQL Editor toolbar, open the Available Databases list, and then choose TFS_Configuration.

3. In the query window, create a stored procedure that executes the stored procedures that you created in each database that TFS 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.

   In the example deployment, the name of the server is FABRIKAMPRIME, and there is only one team project collection in the deployment, the default one created when she installed Team Foundation Server (DefaultCollection). With that in mind, you would create the following script:

   CREATE PROCEDURE sp_SetTransactionLogMarkAll
   @name nvarchar (128)
   AS
   BEGIN TRANSACTION
   EXEC [FABRIKAMPRIME].Tfs_Configuration.dbo.sp_SetTransactionLogMark @name
   EXEC [FABRIKAMPRIME].ReportServer.dbo.sp_SetTransactionLogMark @name
   EXEC [FABRIKAMPRIME].ReportServerTempDB.dbo.sp_SetTransactionLogMark @name
   EXEC [FABRIKAMPRIME].Tfs_DefaultCollection.dbo.sp_SetTransactionLogMark @name
   EXEC [FABRIKAMPRIME].Tfs_Warehouse.dbo.sp_SetTransactionLogMark @name
   COMMIT TRANSACTION
   GO
   

4. Choose the F5 key 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.

5. (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. In SQL Server Management Studio, open a query window, and make sure that SQLCMD Mode is turned on.

2. On the SQL Editor toolbar, open the Available Databases list, and then choose TFS_Configuration.

3. In the query window, enter 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.

4. Choose the F5 key 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.

5. 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 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. In Object Explorer, expand SQL Server Agent, open the Jobs menu, and then choose New Job.

   The New Job window opens.

2. In Name, specify a name for the job. For example, you might choose "MarkTableJob" for your job name.

3. (Optional) In Description, specify a description of the job.

4. In Select a page, choose Steps and then choose New.

5. The New Job Step window opens.

6. In Step Name, specify a name for the step.

7. In Database, choose the name of the configuration database. For example, if your deployment uses the default name for that database, TFS_Configuration, you would choose that database from the drop-down list.

8. Choose Open, browse to the procedure that you created for marking the tables, choose Open two times, and then choose OK.

   Note

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

   EXEC sp_SetTransactionLogMarkAll 'TFSMark'
   

9. In Select a page, choose Schedules, and then choose New.

   The New Job Schedule window opens.

10. In Name, specify a name for the schedule.

11. In Frequency, change the frequency to match the plan that you will create for backing up the databases. For example, you might want to run incremental backups daily at 2 A.M., and full backups on Sunday at 4 A.M. For marking the databases for the incremental backups, you would change the value of Occurs to Daily. When you create another job to mark the databases for the weekly full backup, you would keep the value of Occurs at Daily, and select the Sunday check box.

12. In Daily Frequency, change the occurrence so that the job is scheduled to run one minute before the backup for the databases, and then choose OK. In the example deployment, in the job for the incremental backups, you would specify 1:59 A.M.. In the job for the full backup, you would specify 3:59 A.M..

13. In New Job, choose 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 of the databases that your deployment of TFS uses.

To create a maintenance plan for full backups

1. In SQL Server Management Studio, expand the Management node, open the Maintenance Plans sub-menu, and then choose Maintenance Plan Wizard.

2. On the welcome page for the SQL Server Maintenance Plan Wizard, choose Next.

   The Select Plan Properties page appears.

3. In the Name box, specify a name for the maintenance plan.

   For example, you might create a plan for full backups named TfsFullDataBackup.

4. Choose Single schedule for the entire plan or no schedule, and then choose Change.

5. Under Frequency and Daily Frequency, specify options for your plan. For example, you might specify a weekly backup to occur on Sunday in Frequency, and specify 4 A.M. in Daily Frequency.

   Under Duration, leave the default value, No end date. Choose OK, and then choose Next.

6. 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 choose Next.

7. 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, and then choose Next.

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

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

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

10. 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 choose Next.

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

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

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

14. On the Complete the Wizard page, choose Finish.

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

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 TFS 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. Open SQL Server Management Studio.

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

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

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

   4. Choose Connect.

3. In SQL Server Management Studio, expand the Management node, open the sub-menu, choose Maintenance Plans, and then choose Maintenance Plan Wizard.

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

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

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

6. Choose Single schedule for the entire plan or no schedule, and then choose Change.

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

   Under Duration, leave the default value, No end date. Choose OK, and then choose Next.

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

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

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

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

12. On the Complete the Wizard page, choose Finish.

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

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 TFS 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. Open SQL Server Management Studio.

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

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

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

   3. Choose Connect.

4. In SQL Server Management Studio, expand the Management node, open the submenu, choose Maintenance Plans, and then choose Maintenance Plan Wizard.

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

   The Select Plan Properties page appears.

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

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

7. Choose Single schedule for the entire plan or no schedule, and then choose Change.

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

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

9. Choose OK, and then choose 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 choose 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 choose Next.

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

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 choose Next.

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

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

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

16. On the Complete the Wizard page, choose 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.

Back up the encryption key for reporting services

You must back up the encryption key for Reporting Services as part of backing up your system. Without this encryption key, you will not be able to restore the reporting data. For a single-server deployment of TFS, you can back up the encryption key for SQL Server Reporting Services by using the Reporting Services Configuration tool. You could also choose to use the RSKEYMGMT command-line tool, but the configuration tool is simpler. For more information about RSKEYMGMT, see the following page on the Microsoft website: RSKEYMGMT Utility.

To back up the encryption key by using the Reporting Services Configuration tool

1. On the server that is running Reporting Services, open Reporting Services Configuration Manager.

   The Report Server Installation Instance Selection dialog box opens.

2. Specify the name of the data-tier server and the database instance, and then choose Connect.

3. In the navigation bar on the left side, choose Encryption Keys, and then choose Backup.

   The Encryption Key Information dialog box opens.

4. In File Location, specify the location where you want to store a copy of this key.

   You should consider storing this key on a separate computer from the one that is running Reporting Services.

5. In Password, specify a password for the file.

6. In Confirm Password, specify the password for the file again, and then choose OK.

Create a backup plan for SharePoint Foundation

Unlike Team Foundation Server, which uses the scheduling tools in SQL Server Management Studio, there is no built-in scheduling system for backups in SharePoint Foundation, and SharePoint specifically recommends against any scripting that marks or alters its databases. To schedule backups so that they occur at the same time as the backups for TFS, SharePoint Foundation guidance recommends that you create a backup script by using Windows PowerShell, and then use Windows Task Scheduler to run the backup script at the same time as your scheduled backups of TFS databases. This will help you keep your database backups in sync.

To create scripts to perform full and differential backups of the farm in SharePoint Foundation

1. Open a text editor, such as Notepad.

2. In the text editor, type the following, where BackupFolder is the UNC path to a network share where you will back up your data:

   Backup-SPFarm -Directory BackupFolder -BackupMethod Full
   

   Tip

   There are a number of other parameters you could use when backing up the farm. For more information, see Back up a farm and Backup-SPFarm.

3. Save the script as a .PS1 file. Consider giving the file an obvious name, such as "SharePointFarmFullBackupScript.PS1" or some meaningful equivalent.

4. Open a new file, and create a second backup file, only this time specifying a differential backup:

   Backup-SPFarm -Directory BackupFolder -BackupMethod Differential
   

5. Save the script as a .PS1 file. Consider giving the file an obvious name, such as "SharePointFarmDiffBackupScript.PS1".

   Important

   By default, PowerShell scripts will not execute on your system unless you have changed PowerShell's execution policy to allow scripts to run. For more information, see Running Windows PowerShell Scripts.

After you have created your scripts, you must schedule them to execute following the same schedule and frequency as the schedule you created for backing up Team Foundation Server databases. For example, if you scheduled differential backups to execute daily at 2 A.M., and full backups to occur on Sundays at 4 A.M., you will want to follow the exact same schedule for your farm backups.

To schedule your backups, you must use Windows Task Scheduler. In addition, you must configure the tasks to run using an account with sufficient permissions to read and write to the backup location, as well as permissions to execute backups in SharePoint Foundation. Generally speaking, the simplest way to do this is to use a farm administrator account, but you can use any account as long as all of the following criteria are met:

• The account specified in Windows Task Scheduler is an administrative account.

• The account specified for the Central Administration application pool and the account you specify for running the task have read/write access to the backup location.

• The backup location is accessible from the server running SharePoint Foundation, SQL Server, and Team Foundation Server.

To schedule backups for the farm

1. Choose Start, choose Administrative Tools, and then choose Task Scheduler.

2. In the Actions pane, choose Create Task.

3. On the General tab, in Name, specify a name for this task, such as "Full Farm Backup." In Security options, specify the user account under which to run the task if it is not the account you are using. Then choose Run whether user is logged on or not, and select the Run with highest privileges check box.

4. On the Actions tab, choose New.

   In the New Action window, in Action, choose Start a program. In Program/script, specify the full path and file name of the full farm backup .PS1 script you created, and then choose OK.

5. On the Triggers tab, choose New.

   In the New Trigger window, in Settings, specify the schedule for performing the full backup of the farm. Make sure that this schedule exactly matches the schedule for full backups of the Team Foundation Server databases, including the recurrence schedule, and then choose OK.

6. Review all the information in the tabs, and then choose OK to create the task for the full backup for the farm.

7. In the Actions pane, choose Create Task.

8. On the General tab, in Name, specify a name for this task, such as "Differential Farm Backup." In Security options, specify the user account under which to run the task if it is not the account you are using, choose Run whether user is logged on or not, and select the Run with highest privileges check box.

9. On the Actions tab, choose New.

   In the New Action window, in Action, choose Start a program. In Program/script, specify the full path and file name of the differential farm backup .PS1 script you created, and then choose OK.

10. On the Triggers tab, choose New.

    In the New Trigger window, in Settings, specify the schedule for performing the full backup of the farm. Make sure that this schedule exactly matches the schedule for full backups of the Team Foundation Server databases, including the recurrence schedule, and then choose OK.

11. Review all the information in the tabs, and then choose OK to create the task for the differential backup for the farm.

12. In Active Tasks, refresh the list and make sure that your new tasks are scheduled appropriately, and then close Task Scheduler. For more information about creating and scheduling tasks in Task Scheduler, see Task Scheduler How To.

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.