Restore RedGate Backups using Powershell

This actually iterates a list of folders in a directory, grabs the latest backup from each folder, restores it and then does an integrity check on said restored database.  Unfortuantely, RedGate has no cmdlets for doing this, so it’s strictly passing sql strings into Invoke-sqlcmd based.  This also checks the filelist in the backups and will move the data/log files to a location that you specify.  Use at your own risk.

function RestoreDatabase([string]$servername, [string]$databaseName, [string]$backupFile, [string]$mdfFile, [string]$ldfFile)
{
    try
    {
        $restoreCmd = "
            EXECUTE master..sqlbackup 
            '-SQL `"RESTORE DATABASE [$($databaseName)] 
            FROM DISK = ''$($backupFile)'' 
            WITH RECOVERY, 
            DISCONNECT_EXISTING,  
            REPLACE,
        "
    
        $cmd = "EXECUTE master..sqlbackup '-SQL `"RESTORE FILELISTONLY FROM DISK = [$backupFile]`" '"
        $dataFiles = Invoke-Sqlcmd -ServerInstance $srv -Database 'master' -Query $cmd
        
        foreach($dataFile in $dataFiles)
        {
            if($dataFile.Type -eq "D")
            {
                $move = "MOVE ''$($dataFile.LogicalName)'' TO ''$($mdfFile)" + $dataFile.PhysicalName.Substring($dataFile.PhysicalName.LastIndexOf("\")+1) + "'',"
            }
            if($dataFile.Type -eq "L")
            {
                $move = "MOVE ''$($dataFile.LogicalName)'' TO ''$($ldfFile)" + $dataFile.PhysicalName.Substring($dataFile.PhysicalName.LastIndexOf("\")+1) + "'',"
            }
            $restoreCmd += $move
            $move = ""
        }
        
        $restoreCmd = $restoreCmd.substring(0, $restoreCmd.length-1) + "`"'" 
        Invoke-sqlcmd -serverinstance $srv -database 'master' -query $restoreCmd -QueryTimeout 0 
        
        $sqlout = sqlcmd -E -S $srv -d $databaseName -Q "dbcc checkdb WITH NO_INFOMSGS"
        
        if($sqlout -ne $null)
        {
            Send-MailMessage -SmtpServer $mailServer -To $to -Subject "Database Consistancy Check Failure for $($databaseName)" -Body $sqlout.ToString() -From $from
        }
    }
    catch
    {
        $err = $error[0]|format-list -force  
        send-mailmessage -smtpserver $mailServer -to $to -from $from -subject 'Error in database consistancy checks on $srv' -body $err
        write-host $err
    }

	
}

function Main()
{
    foreach($dir in (gci -path $backupsDir | where{$_.PSIsContainer}))
    {
        $file = gci -path $dir.fullName | where{$_.Name -like "FULL_*" -and $_.Extension -eq ".sqb"} | sort CreationTime -descending | SELECT -first 1
        
        write-host "Restoring database $($dir.name)  --  " (Get-Date -Format "MM/dd/yyyy HH:mm:ss").ToString()
        RestoreDatabase $srv $dir.Name $file.FullName "c:\SQLData\" "c:\SQLLogs\"
        write-host "Finsihed restoring database $($dir.Name)" (Get-Date -Format "MM/dd/yyyy HH:mm:ss").ToString()
    }
}

import-module sqlps -DisableNameChecking

$mailServer = 'mail.yourdomain.com'
$to = 'you@yourdomain.com'
$from = 'donotreply@yourdomain.com'
$backupsDir = "c:\SQLData\Backups"
$srv = "ServerInstance"

main
Advertisements

2 Comments on “Restore RedGate Backups using Powershell

    • Sure, you could do that. I can’t remember the circumstances that I coded this up for, but it *could* have been for restoring older than the latest backups. I’ve got a good memory, it’s just really, really short. 😉

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: