Welcome to Day 22 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at http://blog.waynesheffield.com/wayne/a-month-of-powershell/. Please refer to this page to see all of the posts in this series, and to quickly go to them.

Yesterday we backed up all of our databases. However, what good is a backup if it can’t be restored? Now we’ll restore all those backups into a test database. This script will restore the most recent full backup for each database (into a database named “Verify”), moving all files to a separate directory to avoid trying to restore on top of the existing files. It will then restore the most recent differential backup, and then (if the database is not in the simple recovery model) all transaction log backups since the later of the full / differential backups. This script does assume that if the latest differential backup is more recent that the latest full, that it belongs to that full backup, and the script uses the paths where the databases were backed up to in yesterday’s backup script.

#clear variables
$Server     = $null
 
#Assign variables
$Instance   = "localhost\SQL2008"
$VerifyDB   = 'Verify'
 
#Assign the SMO class to a variable
$SMO        = "Microsoft.SqlServer.Management.Smo"
 
# get the server
$Server = New-Object ("$SMO.Server") "$Instance"
 
# Restoring to a new database changes the $Server.Databases collection, raising an error.
# So, create an empty array to hold the current list of databases and loop through that.
$Databases = @()
# Add each database to this array
ForEach ($db in $Server.Databases)
{
    IF ($db.Status -eq 'Normal' -and $db.IsMirroringEnabled -eq $false -and `
        $db.Name -ne 'tempdb' -and $db.Name -ne $VerifyDB)
    {
        $Databases = $Databases + $db
    }
}
 
ForEach ($db in $Databases)
{
    # Get the location to store the data files at for this verify database
    $Verify = $Server.Settings.DefaultFile
    IF (!($Verify.EndsWith('\'))) {$Verify = $Verify + '\'}
    $Verify = $Verify + "$($VerifyDB)_DBFiles\"
 
    # Ensure that the directory exists
    IF (!(Test-Path -Path $Verify)) {New-Item $Verify -Type Directory}
 
    $dir = $Server.Settings.BackupDirectory + "\" + $db.Name + "\Full\"
    # Get the most recent full backup
    $Full = $Null
    IF (Test-Path -Path $dir)
    {
        $Full = Get-ChildItem $dir |`
            Sort-Object LastWriteTime -Descending |`
            Select-Object -First 1 |`
            Select-Object Name, FullName, LastWriteTime
    }
 
    IF ($Full)
    {
        # Restore the full backup
        $BackupDevice = New-Object ("$SMO.BackupDeviceItem") ($Full.FullName, 'File')
        $Restore = New-Object ("$SMO.Restore")
        $Restore.Checksum = $True
        $Restore.Devices.Add($BackupDevice)
        $Restore.Database = "$VerifyDB"
        $Restore.NoRecovery = $True
        $Restore.ReplaceDatabase = $True
 
        #Relocate the files so that they don't stomp on the existing db files
        ForEach ($FileGroup in $DB.FileGroups)
        {
            ForEach ($File in $FileGroup.Files)
            {
            $FileName = Split-Path $File.FileName -Leaf #Get just the filename
            $FileName = $Verify + $FileName
            $NewFileLoc = New-Object ("$SMO.RelocateFile") ($File.Name, $FileName)
            $Restore.RelocateFiles.Add($NewFileLoc) | Out-Null
            }
        }
 
        ForEach ($File in $DB.LogFiles)
        {
            $FileName = Split-Path $File.FileName -Leaf #Get just the filename
            $FileName = $Verify + $FileName
            $NewFileLoc = New-Object ("$SMO.RelocateFile") ($File.Name, $FileName)
            $Restore.RelocateFiles.Add($NewFileLoc) | Out-Null
        }
        $RelocateFiles = $Restore.RelocateFiles
        Write-Host "Restoring Full backup file $($Full.FullName) to $VerifyDB database"
        $Restore.SqlRestore($Server)
 
        $LastWriteTime = $Full.LastWriteTime
 
        # Get the most recent differential backup
        $Diff = $null
        $TestPath = $Server.Settings.BackupDirectory + "\" + $db.Name + "\Diff\"
        IF (Test-Path -Path $TestPath)
        {
            $Diff = Get-ChildItem $TestPath |`
                Sort-Object LastWriteTime -Descending |`
                Select-Object -First 1 |`
                Select-Object Name, FullName, LastWriteTime
 
            # if the latest diff was before the latest full, don't do anything with it.
            IF ($Diff.LastWriteTime -LT $Full.LastWriteTime) {$Diff = $null}
            ELSE {$LastWriteTime = $Diff.LastWriteTime}
        }
 
        # If there is one,
        IF ($Diff)
        {
            $BackupDevice = New-Object ("$SMO.BackupDeviceItem") ($Diff.FullName, 'File')
            $Restore = New-Object ("$SMO.Restore")
            $Restore.Checksum = $True
            $Restore.Database = "$VerifyDB"
            $Restore.Devices.Add($BackupDevice)
            $Restore.NoRecovery = $True
            ForEach ($RelocateFile in $RelocateFiles) {$Restore.RelocateFiles.Add($RelocateFile) | Out-Null}
            Write-Host "Restoring Differential backup file $($Diff.FullName) to $VerifyDB database"
            $Restore.SqlRestore($Server)
        }
 
        IF ($DB.RecoveryModel -ne "Simple")
        {
            # Restore all transaction logs written since the last full/diff backup
            $TestPath = $Server.Settings.BackupDirectory + "\" + $db.Name + "\Log\"
            IF (Test-Path -Path $TestPath)
            {
                ForEach ($File in Get-ChildItem $TestPath |`
                            Where {$_.LastWriteTime -ge $LastWriteTime} |`
                            Sort-Object LastWriteTime |`
                            Select-Object Name, FullName )
                {
                    $BackupDevice = New-Object ("$SMO.BackupDeviceItem") ($File.FullName, 'File')
                    $Restore = New-Object ("$SMO.Restore")
                    $Restore.Checksum = $True
                    $Restore.Database = "$VerifyDB"
                    $Restore.Devices.Add($BackupDevice)
                    $Restore.NoRecovery = $True
                    ForEach ($RelocateFile in $RelocateFiles) {$Restore.RelocateFiles.Add($RelocateFile) | Out-Null}
                    Write-Host "Restoring Transaction Log backup file $($File.FullName) to $VerifyDB database"
                    $Restore.SqlRestore($Server)
                }
            }
        }
        # Bring the database online
        $Restore.NoRecovery = $False
        Write-Host "Bringing $VerifyDB database online"
        $Restore.SqlRestore($Server)
        Write-Host "Test Restore of the [$($db.name)] database passed"
 
        # This would be a good time to do a checkdb!
    }
}

If you are utilizing SMO from SQL Server 2012, a new method has been added: Restore-SqlDatabase. This cmdlet has parameters to control all aspects of the restore.