#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!
    }
}