Backup Team Foundation Server 2012- Best Practices
This blog is written for Team Foundation Server 2012 and it is also applicable for Team Foundation Server 2010.
We must backup TFS deployment with a regular and scheduled plan for the following reasons
1. To recover data loss
2. For disaster management
3. Upgrade of data tier.
4. Move of TFS deployment server.
Backup Strategy
1. We must first back up all databases for TFS.
2. If deployment includes SharePoint Products or SQL Server Reporting Services, we must also back up the databases that TFS uses within those components.
3. We must not forget to take backup of Reporting Services encryption key.
4. To prevent synchronization errors or data mismatch errors, you must synchronize all backups to the same time stamp.
5. When we deploy Team Foundation, you should keep a record of the accounts that you create and any computer names, passwords, and setup options that you specify.
6. We should also keep a copy of all recovery materials, documents, and database and transaction log backups at a secure location.
7. To safeguard against a disaster, such as a fire or an earthquake, you should keep duplicates of your server backups in a different location from the location of the servers. This strategy will help protect you against the loss of critical data. As a best practice, you should keep three copies of the backup media, and you should keep at least one copy offsite in a controlled environment.
8. Perform a trial data restoration periodically to verify that your files are correctly backed up. A trial restoration can reveal hardware problems that do not appear with software verifications.
Must backup the following databases in TFS Deployment
1. Configuration Database (Tfs_Configuration)
2. Warehouse Database (Tfs_Warehouse)
3. Team Project Collection databases (Tfs_DefaultCollection)
4. SharePoint Products administrative databases and the site collection databases (WSS_Content, WSS_Configuration & WSS_AdminContent)
5. Database for Reporting Services (ReportServer & ReportServer_TempDB)
6. Database for Analysis Services (Tfs_ Analysis)
We can back up the databases for TFS deployment using the 2 ways
1. Manually Back Up Team Foundation Server
This feature is available in ‘Team Foundation Server Admin Console’ after installing ‘Team Foundation Server Update 2’ or ‘Team Foundation Server Update 3’. Or we can use SQL Server Management Studio to create backup plan.
Please refer the following link for more details on ‘How to manually back up TFS databases’
https://msdn.microsoft.com/en-us/library/ms253070(v=vs.110).aspx
Permissions to perform manual backup
For schedule backup through Team Foundation Admin console, one need the following permissions
At SQL Server instance following roles are assigned
1. Dbcreator
2. public
Using Selected Database following roles are to be assigned
1. db_backupoperator
2. public
2. Scheduled backup feature of TFS 2012
This feature is available in ‘Team Foundation Server Admin Console’ after installing ‘Team Foundation Server Update 2’ or ‘Team Foundation Server Update 3’.
Permissions to perform scheduled backup
One must be a member of both of the following groups:
1. The Administrators security group on the server that is running the administration console for Team Foundation.
2. The SQL Server System Administrator security group. In addition, the service account for TFS (TFSService) must have SQL Server Perform Back Up and Create Maintenance Plan permissions set to Allow on each instance of SQL Server that hosts the databases that you want to back up.
3. The Farm Administrators group in SharePoint Foundation 2010, or an account with the permissions required to back up the farm.
Note: To use backup feature in admin console of Team Foundation Server 2010, we need to install ‘Power Tools for Team Foundation Server 2010 ’.
Please refer below link to install ‘Power Tools for Team Foundation Server 2010 ’.
https://visualstudiogallery.msdn.microsoft.com/c255a1e4-04ba-4f68-8f4e-cd473d6b971f
Please refer the following link for more details on ‘Backup TFS databases using TFS admin console’.
https://blogs.msdn.com/b/bharry/archive/2010/08/18/backing-up-and-restoring-your-tfs-server.aspx
Recovery Models for Databases
Following are the types of backup which we can choose as per TFS deployment.
1. Full Data Backups Recovery Model (FDB Recovery Model)
Complete database backup at the time of backup. We should use this model when we need to use this model when the size of databases is small and the databases that can be backed up quickly.
2. Differential Data Backups Recovery Model (DDB Recovery Model)
This model backs up the parts of the database that is changed since the last Full Database backup. We should use this model when we need to recover the database to the point of failure or to another specific point in time. This should be used when size of databases is too large and takes lot of time to take full backup.
As a best practice when we use this recovery model, we recommend the following guidelines
1. After a full database backup, schedule differential database backups periodically. For example, you might take a differential database backup every four hours or, for highly active systems, even more frequently.
2. At an interval that makes sure that differential backups do not become too large, schedule a new full database backup. For example, we might back up the full database one time per week.
3. Transactional Log Backups Recovery Model (TLB Recovery Model)
Backups up the transaction log files.
· Pure Log Backup
A pure log backup contains only transaction log records for an interval, without any bulk changes. Refer following link for more details-
· Bulk Log Backup
Bulk-logged recovery model minimally logs bulk operations. Refer following link for more details-
https://technet.microsoft.com/en-us/library/ms190692(v=sql.105).aspx
· Tail Log Backup
A tail-log backup captures any log records that have not yet been backed up (the tail of the log) to prevent work loss and to keep the log chain intact. Refer following link for more details-
Marked Transaction
Marked transactions are used to restore databases to a specific recovery point. Marked transaction is very useful if we need to restore a table to a transaction.
To manually back up Team Foundation Server, we must not only back up all databases that the deployment uses, we must also synchronize the backups to the same point in time. We can manage this synchronization most effectively if you use marked transactions. If we routinely mark related transactions in every database that Team Foundation uses, we establish a series of common recovery points in those databases. If we regularly back up those databases, we reduce the risk of losing productivity or data because of equipment failure or other unexpected events.
To achieve marked transactions we need to perform following steps.
1. To make sure that all databases are restored to the same point, you can create a table in each database to mark transactions.
2. After the tables have been created in each database that you want to back up, you must create a procedure for marking the tables.
3. 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.
4. 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.
5. 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.
For step by step process for performing above steps, check https://msdn.microsoft.com/en-us/library/ms253070.aspx#MarkTbl
CHECKDB
We should use this command to check integrity of TFS databases. If the result of this command is an error, we must restore databases from backup. This command can be used in following situations
1. If we lost some data and we are unsure about it.
2. In situation of disaster.
3. Before and after upgrade of data tier.
4. Before and after moving of TFS deployment server.
Checks the logical and physical integrity of all the objects in the specified database by performing the following operations:
· Runs DBCC CHECKALLOC on the database.
· Runs DBCC CHECKTABLE on every table and view in the database.
· Runs DBCC CHECKCATALOG on the database.
· Validates link-level consistency between table metadata and file system directories and files when storing varbinary (max) data in the file system using FILESTREAM.
· Validates the Service Broker data in the database.
· Validates the contents of every indexed view in the database.
· Validates link-level consistency between table metadata and file system directories and files when storing varbinary (max) data in the file system using FILESTREAM.
· Validates the Service Broker data in the database.
This means that the DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKCATALOG commands do not have to be run separately from DBCC CHECKDB
Refer following link for more details- https://technet.microsoft.com/en-us/library/ms176064.aspx
Content created by – Anurag Verma
Content reviewed by – Romit Gulati