Backing Up Team Foundation Server

Backing up Team Foundation Server mostly requires backing up the data tier. In addition, you need to back up the symmetric encryption key from the report server.

Backing Up the Team Foundation Server Databases

Eleven databases, shown in the following table, make up the Team Foundation Server data tier:

Open table as spreadsheet

Database Name	Database Function
ReportServer	This is the SQL Server Reporting Services database. It contains all the reports, settings, and other information related to Reporting Services.
ReportServerTempDB	This is used by SQL Server Reporting Services to temporarily hold information needed for running reports.
STS_Config	This is used by Windows SharePoint Services to hold configuration information for the different Team Project WSS sites.
STS_Content_TFS	This is used by Windows SharePoint Services to hold the content of the different Team Project WSS sites.
TfsActivityLogging	This is used by Team Foundation Server to maintain a history of all Web server requests made to Team Foundation Server.
TfsBuild	This is used by Team Foundation Server to hold all build information and related data, such as test results.
TfsIntegration	This is used by Team Foundation Server to hold registration data on projects, areas, and iterations, among other items.
TfsVersionControl	This is used by Team Foundation Server to hold all information related to the Team Foundation version control system.
TFSWarehouse	This is the SQL Server Analysis Server database used by Team Foundation Server.
TfsWorkItemTracking	This is used by Team Foundation Server to hold all information related to work item tracking.
TfsWorkItemTrackingAttachments	This is used by Team Foundation Server to hold all attachments added to the work item tracking system.

Before you can back up these databases, or any database for that matter, you need to determine your priorities and strategy for backing up the information. This is a huge and detailed topic, and Microsoft has some very in-depth information for you on this. Please reference "Backing Up and Restoring Databases" in the SQL Server 2005 help (http://msdn2.microsoft.com/en-us/library/ms186289.aspx) for more information. Also, you need to make sure you have installed SQL Server Integration Services on the database. If you have not, you can simply reinsert your SQL Server 2005 media, restart the installation, and choose that option to install.

To back up these eleven databases, you can use the built-in tools provided by SQL Server 2005. Using SQL Server Management Studio, you can set up a maintenance plan to back up the databases as you feel appropriate. You can set up an individual maintenance plan for each database, or can back up all the databases using one maintenance plan. In the following steps, you learn how to set up a single maintenance plan to run a full backup on all the user databases. You can easily extrapolate these steps to set up differential backups as well, or to back up individual databases.

You will be performing all the following on the data-tier server.

1. Open the SQL Server Management Studio, by going to Start⇨All Programs⇨Microsoft SQL Server 2005, and then selecting SQL Server Management Studio. This opens the SQL Server Management Studio window, and prompts you for your connection information, as shown in Figure 5-1.

   More than likely you are already logged in as the appropriate user if you are doing administrative tasks, so you should just be able to click Connect and move on. However, you can modify the information in the login box to change which server you are connecting to, the type of authentication you wish to use, and enter a valid user name and password, if required.

2. When you are in Management Studio, you need to kick off the Maintenance Plan Wizard to set up a new maintenance plan. On the left side of the Management Studio is a tree-view structure of the database server. Click the plus sign next to the Management folder to expand that folder. Right-click the Maintenance Plans option, and select Maintenance Plan Wizard from the context menu. As you would expect, this starts the Maintenance Plan Wizard.

   The first page of the wizard is just a welcome page. Click Next to move to the Select a Target Server page, shown in Figure 5-2.

   On this page you can name the maintenance plan, as well as give it a description. Best practices recommend you give the plan a descriptive name, so you can easily see the purpose of the plan. For this example, name the plan TeamFoundationServerFullBackup. You can also select which SQL Server to back up, how the backup user authenticates to the server, and even which user to use for running the maintenance plan. Leave these as their defaults, and click Next.

3. Figure 5-3 shows the next step of the wizard, the Select Maintenance Tasks page. You can choose to have a maintenance plan perform multiple functions, including shrinking the database or rebuilding indexes. To do so, simply check the check box of the task you wish to run. As mentioned earlier, you are doing a full backup in this example, so select Back Up Database (Full) and click Next.

4. This opens the Select Maintenance Tasks Order page. If you selected multiple tasks to perform in the previous step, you use this page to put the tasks in the order you wish them to be performed. For example, if you selected both the Check Database Integrity task and the Back Up Database (Full) task, you might want the integrity task to run before the full database backup is performed. You would simply select the Check Database Integrity task in this window, and use the Move Up button to move it to the top of the list. You can also use the Move Down button to move tasks closer to the bottom of the list, in effect making them run after other tasks. In this example, you only have one task, so accept the defaults on this page and click Next.

5. This opens the Define Back Up Database (Full) Task page, shown in Figure 5-4. On this page, you can select the databases to back up, the type of backup to perform, and the backup destination, such as to a file or a tape drive. You can also specify whether the databases should be backed up across multiple files, or if a backup file should be created for each database being backed up. You can choose the folder to store the backup files in, the extension to give the files, and even create a subdirectory in the backup folder for each database, allowing for easier organization of your backups. Finally, you can verify the integrity of the backup once it has been performed, to ensure there has been no corruption of the backup files.

   When you select a database from the drop-down list box, you are presented with four radio button options:

   • All databases

   • All system databases (master, msdb, model)

   • All user databases (excluding master, msdb, model, tempdb)

   • These databases, which is a check box list of all the databases on the server

   Simply select the appropriate radio button for the task you are working on. For this example, from the Database drop-down list box, select All User Databases radio button, and click OK. This will back up all nonsystem databases.

   Important

   You should create a separate maintenance plan to back up the system databases used by SQL Server 2005.

6. Next you need to specify where the backup will be placed. Ideally, you would like the backups to be placed on a separate machine, which then archives the backup to tape or some other archival media. It is not recommended to backup directly to tape, as this will increase your backup time.

   For this example, select the Create a backup file for every database radio button, and check the Create a sub-directory for each database check box. In the Folder text box, enter c:\backups. Make sure this directory exists before you execute the maintenance plan. Optionally, you can check the Verify backup integrity check box. Once you have configured the backup task, click Next. This opens the Select Plan Properties window, shown in Figure 5-5.

7. A maintenance plan defaults to Not Scheduled, meaning it will run only if you execute it. Ideally, you want your full backup to run weekly, or possibly even daily. For this example, you want to set the maintenance plan to run nightly at midnight. To do this, you click the Change button to open the New Job Schedule screen, shown in Figure 5-6.

   This creates a new job for the SQL Server to run, which will execute this full database backup maintenance plan. You have a variety of options on this page. You can name the job, and enable or disable it from running using the Enabled check box. You can select the appropriate schedule type from the following options:

   • Start Automatically when SQL Server Agent Starts

   • Start whenever the CPU(s) become idle

   • Recurring

   • One Time

   For a One Time schedule, fill out the One Time Occurrence section, giving the job a date and time to run. For Recurring schedules, which will probably be the most used option, fill out the Frequency, Daily frequency, and Duration sections, as needed. Notice the Summary Description at the bottom of the page; it shows, in plain words, when the job is set to run.

   For this example, enter in the name of your job. In this case, you can use the same name as the maintenance plan, TeamFoundationServerFullBackup. Set the Schedule Type to Recurring, the Frequency Occurs to Daily, and the Daily Frequency to 12:00:00 AM. Click OK to return to the Select Plan Properties window, and then click Next.

8. This opens the Select Report Options window. You can have the maintenance plan write a report out to specific directory. You can also have the report e-mailed. Clicking Next takes you to the final step of the wizard, the Complete the Wizard page.

9. Here you can review all the options you have chosen for your maintenance plan, and verify everything is correct before you have the plan built. If everything looks good, click Finish to build the plan.

Figure 5-1

Figure 5-2

Figure 5-3

Figure 5-4

Figure 5-5

Figure 5-6

A progress window opens and updates you on the creation of the maintenance plan. When the wizard is finished, you are alerted to the success or failure of the maintenance plan creation.

At this point in SQL Server Management Studio, in the Management Folder, and then in the Maintenance Plan folder, you can see the maintenance plan you just created. If you drill down under the SQL Server Agent node, and then the Jobs folder, you will see the job that was created to execute the maintenance plan nightly.

If you want to go ahead and run your maintenance plan, to ensure everything works, simply right-click the maintenance plan and select Execute from the context menu. Your maintenance plan will run, and you can verify the databases were backed up as expected.

Backing Up the Report Server Encryption Key

Besides backing up the databases, you also need to back up the SQL Server Reporting Services encryption key. SQL Server Reporting Services uses encryption keys to secure connections and other vital information. These keys are created during the installation of the server, and are required for the server to run. There are three types of keys used by Reporting Services: public, private, and symmetric. For purposes of this discussion, what each key is used for is not relevant. What is important, however, is that you back up the symmetric key. Without the symmetric key, Reporting Services will not work. If you have to restore the databases to a new server, and do not have a backup of the symmetric key, all the Reporting Services information will be useless.

Backing up the symmetric encryption key writes out a file containing the key. The key itself is scrambled using a password you have chosen when the file was created. You can then place this file in a directory where your backup software will find and archive it. Don't forget the password you used to create the file with. Without the password, you cannot recover the symmetric key from the file.

1. Go to Start⇨All Programs⇨Microsoft SQL Server 2005⇨Configuration Tools, and select Reporting Services Configuration. This starts the SQL Reporting Services Configuration tool. Enter the machine name and the instance name of the Report Server you wish to configure, as shown in Figure 5-7.

2. Enter in the appropriate information, and click Connect to open the configuration tool itself. This tool allows you to configure all the various aspects of the Report Server. This includes modifying virtual directories, service identities, and e-mail settings, just to name a few. Once in the configuration tool, click the Encryption Keys link on the left side of the tool. This opens the Encryption Key section of the tool, shown in Figure 5-8.

3. You have several different options here, including backing up, restoring, changing, or deleting the encryption key. You want to back up the key, so click the Backup button. Figure 5-9 shows the Encryption Key Information window that opens.

4. In the Password field, you enter a value to help scramble the key file. Do not forget what you enter here! Otherwise, the key file will be useless. You can click the ellipse button to select a directory and a name for your key file. Click OK to create the file. Once the file has been created, you should verify the file exists, and then archive it accordingly. Again, not to belabor a point, do not forget the password for your key file!

Figure 5-7

Figure 5-8

Figure 5-9

Instead of using the Reporting Services Configuration tool to back up the key file, you can use the RSKEYMGMT command-line tool. If you have deployed Team Foundation Server in a dual server or a cluster environment, you will have to use this tool in those instances, as the application tier does not support the Reporting Services Configuration tool described above.

The RSKEYMGMT command-line tool is located by default in the C:\Program Files\Microsoft SQL Server\90\Tools\Binn folder. The syntax for using this tool to backup the encryption key is:

 RSKEYMGMT-e-f <FileName> -p <password> 

So, to do the same example as above, but with the command-line tool, you would run the following:

 RSKEYMGMT-e-f c:\backups\ReportServerKey.snk-p MyPassword 

Restoring the Report Server Encryption Key (Optional)

If the encryption key for the report server has changed between the current state of the report server and the restore date of the backup files, then you will need to restore the report server encryption key. If the key has not changed, then you do not need to do anything, and can skip this section.

1. To restore the encryption key, open the Reporting Services Configuration Tool, as explained earlier. Enter the appropriate connection information and click Connect. Select Encryption Keys from the left side of the screen, then click Restore.

2. This opens the Encryption Key Information window, as you saw in Figure 5-9. Enter the password you selected for your key file, then click the ellipse button and select your key file.

3. Click OK to accept this file and password, and restore the key file to the reports server.

You have now backed up all the information necessary to restore Team Foundation Server. Up next, you learn how to restore that information.