Skip to content

February 7, 2013


Restore RedGate Backups using Powershell

by Scott Newman

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)
        $restoreCmd = "
            EXECUTE master..sqlbackup 
            '-SQL `"RESTORE DATABASE [$($databaseName)] 
            FROM DISK = ''$($backupFile)'' 
            WITH RECOVERY, 
        $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
        $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 $($  --  " (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 = ''
$to = ''
$from = ''
$backupsDir = "c:\SQLData\Backups"
$srv = "ServerInstance"

Read more from Powershell, SQL Server
2 Comments Post a comment
  1. Iain Barnetson
    Jan 11 2017

    Why not use the “LATEST_ALL”, “LATEST_FULL” or “LATEST_DIFF” commands rather than searching with gci ?
    I do this: “SOURCE = ”$SourceDB” LATEST_ALL”

    • Jan 11 2017

      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: Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

Note: HTML is allowed. Your email address will never be published.

Subscribe to comments

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: