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

Memo to self - listing databases status in Powershell

Very quick and dirty. Subject to evolve onto. Also sends email with a status report as well as an email in case any of the databases is not in the "Normal" state.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 

$Server = new-object ('Microsoft.SqlServer.Management.Smo.Server') '.'

$stats = "";
$stats += "Databases status for server {0}:`n" -f $Server.Name

foreach ($db in $Server.Databases)
{
    $stats += "{0, 25} `tStatus: {1}`n" -f $db.Name, $db.Status
    if ($db.Status.toString() -ne "Normal")
    {
        ## Notify by email
        $sc = new-object Net.Mail.SmtpClient -arg $EmailServer
        $sc.Send($EmailFrom, $EmailTo, "Error: Databases check on server {0} identified db [{1}] that is in {2} state at {3}" -f $Server.Name, $db.Name, $db.Status, $(Get-Date), $stats)
    }
}

write-Output $stats

## Notify by email
$sc = new-object Net.Mail.SmtpClient -arg $EmailServer
$sc.Send($EmailFrom, $EmailTo, $EmailSubjectPrefix + " Databases check was executed at " + $(Get-Date), $stats)

Technorati Tags: powershell, sql server

VSCmdShell - Microsoft.VSPowerToys.VSCmdShell.PowerShellHost.dll to work with Powershell 1.0 RTM

As latest discussion in the codeplex for  VSCmdShell  (http://www.codeplex.com/VSCmdShell/Thread/View.aspx?ThreadId=2189) is 1 month old and there is no patched release yet, I've compiled the source to get the correct Microsoft.VSPowerToys.VSCmdShell.PowerShellHost.dll. Just copy it into the Program Files\Power Toys for Visual Studio\VSCmdShell folder after the add-in is installed.

Technorati Tags: powershell, vs2005

Note to self - piping sql scripts from the directory to the osql - Powershell

Get-ChildItem C:\SomePath -recurse | Where-Object {$_.get_Extension() -eq ".sql"} |foreach {Write-Output "Executing: " $_.FullName; osql -S SqlServerName -d DatabaseName -i $_.FullName -E} > c:\SomeFileToPipeOutputTo TODO: redo to use sqlcmd as osql is not recommended anymore for the sql 2005