TFS Backup

Backup share is required by the TFS backup wizard. Actually the wizard checks if the share is on the local host. To be sure that the rights are correct for a SQL Server backup the SQL Server service account should be owner of the folder that is shared. Subfolders should be created with the (undocumented) extended stored procedure xp_create_subdir to ensure that the SQL Server service account has the correct rights on the given folder.

Backup by TFS Job Tasks defined in TFS Administration Console (TfsMgmt.exe). Please be avare of that files are written in two contexts - the TFS Service (TFSService) and the SQL Server service:

• Recovery Model on all TFS and SSRS databases is set to Full in the context of the administrator.
• Backup configuration is written in xml-file in TFS backup folder (BackupSettings.xml) in the context of the TFS Service.
• Backup of Reporting Services encryption keys are done by the wizard - not by a TFS backup job. This is why there only is one snk-file on a key backup in the TFS backup share.

Actual process in backup job:

1. Verify installation, running status and capacity.
2. Prepare databases for synchronized backup
3. Grant permissions to backup path
4. Full database backup of TFS databases; Configuration, Collection(-s) and Warehouse. Also SSRS databases; ReportServer and ReportServerTempDB. Backup files are created and written inthe context of the SQL Server service - like a normal SQL Server backup.
5. Marking TFS databases using stored procedure prc_SetTransactionMark in each database; EXEC prc_SetTransactionMark TfsMarkTfpt
6. Backup transaction log on TFS and SSRS databases. Backup files are created and written in the context of the SQL Server service.

Log files on the TFS job task are in the folder "%ProgramData%\Microsoft\Team Foundation\Server Configuration\Logs".

Also configuration and log files on each TFS backup run in the TFS backup folder.

• Transactional_<number>.log; TFS job log on transaction log backup.
• <database name>_<number>L.trn; SQL Server transaction log backup file.
• Delete_<number>.log; TFS job log on deletion of old files.
• Full_<number>.log; TFS job log on full database backup.
• <database name>_number>F.bak; SQL Server full database backup file.
• BackupSets.xml; TFS backup history on existing backup files.
• RSKey<number>.snk; SQL Server Reporting Services encryption key backup file.

The log file BackupSets.xml holds information about the the existing backup files and their history.
Existing backup files is controlled by the TFS Delete job that deletes backup files on the given retention period when the recommended minimum value is seven days.

TFS Scheduled Backups does not

• back up system databases; master, msdb, model. You could use Ola's SQL Server Maintenance Solution (GitHub).
• back up Service Master Key (SMK).
• back up Database Master Key (DMK) on master.
• verify backup files.