Sample scripts for backing up all TFS related databases

 

Powershell part:

# Set sql server
[string] $SqlServerName = "[TFSSQLServerName]"

# Set date and time driven variables
$date = get-Date
[string] $DateSuffix = $date.ToString("yyyyMMdd")
[string] $DateTimeSuffix = $date.ToString("yyyyMMddTHHmmssfffz").Replace("+", "U");
[string] $RootPath = "E:\DBBackups\"
[string] $BackupSqlScriptPath = "C:\....\BackupAllTfsDatabases.sql"
[string] $TargetPath = $RootPath + $DateSuffix + "_"
[int] $i = 0
do
{
    $i++
    $directoryExists = test-Path ($TargetPath + $i + "\")
}
while ($directoryExists -eq $true)

$TargetPath = $TargetPath + $i + "\";

if ($dir -eq $null)
{
    Write-Output "Directory created: $TargetPath"
    new-item -path $TargetPath -type directory
}
else
{
    Write-Output "Directory already existed: $TargetPath"
}

Write-Output "Generating set of backups to disk: $TargetPath, with datetime siffix of $DateTimeSuffix"
[string] $TargetPathForCmd = '"' +  $TargetPath + '"' #Simply putting qoutes around in the command line itself doesn't work

$sqlCmdOutput = sqlcmd -S $SqlServerName -E -p -i $BackupSqlScriptPath -v RootPath=$TargetPathForCmd DateSuffix=$DateSuffix DateTimeSuffix=$DateTimeSuffix 2>&1

$sqlCmdOutput

SQL Part: 

SET NOCOUNT ON

DECLARE @DateSuffix nvarchar(30),
        @DateTimeSuffix nvarchar(30),
        @RootPath nvarchar(200),
        @BackupName nvarchar(100),
        @BackupFile nvarchar(255),
        @TodaysDirectory nvarchar(30),
        @DBName nvarchar(30)

DECLARE @databases table (DBName nvarchar(30))
-- Add an insert here for any new database to backup
INSERT INTO @databases (DBName) VALUES (N'ReportServer')
INSERT INTO @databases (DBName) VALUES (N'STS_Config_TFS')
INSERT INTO @databases (DBName) VALUES (N'STS_Content_TFS')
INSERT INTO @databases (DBName) VALUES (N'TfsActivityLogging')
INSERT INTO @databases (DBName) VALUES (N'TfsBuild')
INSERT INTO @databases (DBName) VALUES (N'TfsIntegration')
INSERT INTO @databases (DBName) VALUES (N'TfsVersionControl')
INSERT INTO @databases (DBName) VALUES (N'TfsWarehouse')
INSERT INTO @databases (DBName) VALUES (N'TfsWorkItemTracking')
-- Set day and time based parameters
SET @DateSuffix = N'$(DateSuffix)'
SET @DateTimeSuffix = N'$(DateTimeSuffix)'
SET @RootPath = N'$(RootPath)' --N'E:\DBBackups\'
SET @TodaysDirectory = @RootPath -- + @DateSuffix + N'\'

-- Setup and run a cursor
DECLARE CursorOverDatabases CURSOR FOR SELECT DBName FROM @databases

OPEN CursorOverDatabases

FETCH NEXT FROM CursorOverDatabases INTO @DBName

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @BackupName = @DBName + N'_' + @DateTimeSuffix
        SET @BackupFile = @TodaysDirectory + @BackupName + N'.bak'

        BACKUP DATABASE @DBName TO  DISK = @BackupFile
            WITH NOFORMAT, NOINIT,  NAME = @BackupName, SKIP, REWIND, NOUNLOAD,  STATS = 10

        DECLARE @backupSetId as int
        SELECT @backupSetId = position from msdb..backupset where database_name=@DBName and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=@DBName)
        IF @backupSetId is null
            BEGIN RAISERROR(N'Verify failed. Backup information for database %s not found.', 16, 1, @DBName) END

        RESTORE VERIFYONLY FROM  DISK = @BackupFile
            WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

        FETCH NEXT FROM CursorOverDatabases INTO @DBName
    END
CLOSE CursorOverDatabases
DEALLOCATE CursorOverDatabases