Skip to content

February 7, 2013

2

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)
{
    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
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”

    https://documentation.red-gate.com/display/SBU7/The+RESTORE+command

    Reply
    • 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. 😉

      Reply

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 )

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

%d bloggers like this: