Verifying SQL Server Database Backups with #PowerShell

Like all DBAs I’m a bit obsessive about database backups. And, of course, an untested backup isn’t much of a backup at all (my IO subsystems could be corrupting my backups RIGHT NOW). But, testing your backups is time consuming, dull work. So, to satisfy my needs for validated backups and my highly tuned aversion to repetitive tasks, I’ve automated this process. This is a PowerShell script that, given an array of servers, will read the past 24 hour backup history and restore the oldest full backup that occurred along with all log backups to the present point in time. Every day I get an email telling me (mostly) good things about my database backups and that means the tiny flame of life still burning in this DBAs jaded heart burns a little brighter. And if I do have a problem (I have issues with log backups from time to time), it tells me that, too.

Of course, this script is written with a certain environment in mind. For instance, I have the luxury of being able to take a full backup every night for my critical databases. You may have to tweak it to work for you. I also use Idera’s SQL Safe (but I don’t use it for log backups), so I’ve included code for both standard .bak and .safe files

There are also a lot of ways to improve / expand this script. For instance, it only works on databases with one data file, which will be limiting to a lot of people. But, I hope this provides a foundation to build something that works in your environment.

<############################################################################################################

.SYNOPSIS
   This script will, for all databases on a list of servers (except for master), restore the most recent full
   backup and any log backups that it finds up to the current time. 
   
.Notes
    AUTHOR: Jon Boulineau
    LASTEDIT: 26 October 2011
.Link
     http://newsqlblog.com
     
############################################################################################################>

# Use Chad Miller's Invoke-Sqlcmd2 to avoid SQLPS
# www.sev17.com
function Invoke-Sqlcmd2
{
	param
	(
	[string]$ServerInstance,
	[string]$Database,
	[string]$Query,
    # IMPORTANT MODIFICATION: 
    # The default timeout of 30 seconds which Chad set is insufficient for the purpose.  If a restore takes longer, it will give up
    # on the process and kick off the next restore in the list (also creating errors in the report).  7200 seconds is 2 hours, but 
    # adjust as needed.  Of course, in any legitimate timeout situation you could end up with a very very long running script. This can
    # also be set to 0 for an infinite timeout.  More info: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx
	[Int32]$QueryTimeout = 7200
	)

	$conn = new-object System.Data.SqlClient.SQLConnection
	$conn.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $ServerInstance,$Database
	$conn.Open()
	$cmd = new-object system.Data.SqlClient.SqlCommand($Query,$conn)
	$cmd.CommandTimeout=$QueryTimeout
	$ds = New-Object system.Data.DataSet
	$da = New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
	[void]$da.fill($ds)
	$conn.Close()
	$ds.Tables[0]
}

<####################### SET THESE VARIABLES TO CONFORM TO YOUR ENVIRONMENT ######################>

[Array]$serverNames = ""

# Specify the server to which the restores will be made.  BE CAREFUL WITH THIS !!!
[String]$testServer = ""

[String]$dataPath = "" 		# e:\SQLFiles\
[String]$logPath = "" 		# e:\SQLFiles\
[String]$SMTPServer = ""	# mail.mydomain.com
[String]$EmailTo = ""		# dba@mydomain.com
[String]$EmailFrom = ""		# admin@mydomain.com

# These are only necessary if you're using Idera SQLSafe
[String]$windowsUsername = "<Username>";
[String]$windowsPassword = "<EncryptedPassword>";

<####################### SET THESE VARIABLES TO CONFORM TO YOUR ENVIRONMENT ######################>


# There's probably a better way to format this using ConvertTo-HTML
[String]$resultHTMLTable = "<html><body><table>";

$historyQuery = 
    "SELECT    
        [bs].[database_name]
        , [bs].[backup_finish_date]
        , [bs].[server_name]
        , [bs].[type]
        , [mf].[physical_device_name]
    FROM
        [msdb].[dbo].[backupset] bs
        INNER JOIN [msdb].[dbo].[backupmediafamily] mf ON [bs].[media_set_id] = [mf].[media_set_id]
    WHERE
        DATEDIFF(hh , [bs].[backup_finish_date] , CURRENT_TIMESTAMP) <= 24
        AND [mf].[family_sequence_number] = 1"

foreach($name in $serverNames)
{

	# Get a list of the databases on the server
	$databases = Invoke-Sqlcmd2 -ServerInstance $name -Database master -Query "SELECT name FROM sys.databases WHERE name NOT IN ('tempdb','master')"

	# Get the entire backup history for the last 24 hours
	$backupSets = Invoke-Sqlcmd2 -ServerInstance $name -Database "master" -Query $historyQuery

	#For each of the databases, check for backups
	foreach($db in $databases)
	{
        clear;
		$resultHTMLTable = $resultHTMLTable + "<tr><td>$name</td><td>$($db.Name)</td>";
		
        # Note : This only works when one log or data file exists. 
		$dataPath = $dataPath + $($db.Name)_data.mdf
		$logPath = $logPath + $($db.Name)_log.ldf

		# We will need to bring the database online when the restores are complete
		$recoverQuery = "RESTORE DATABASE $($db.Name)"; 
		
		$dropQuery = "DROP DATABASE $($db.Name)";

		# Capture the most recent full backup
		$fullBackup = $backupSets | Where-Object{$_.database_name -eq $db.name -and $_.type -eq "D" } | Sort-Object -Property @{Expression={$_.backup_finish_date}; Ascending=$false} | Select-Object -First 1 ;

		if($fullBackup -eq $null) 
		{
			$resultHTMLTable = $resultHTMLTable + "<tr><td>$($db.Name)</td><td>Backups do not exist</td></tr>";
		}
		else
		{
       
			# Store an ordered list of log backups for the database
            $logBackups = $backupSets | Where-Object{$_.database_name -eq $db.name -and $_.type.ToString() -eq "L" -and $_.backup_finish_date -ge $($fullBackup.backup_finish_date)} | Sort-Object -Property @{Expression={$_.backup_finish_date}; Ascending=$true} ;

            # Idera
			#$fileList = Invoke-Sqlcmd2 -ServerInstance $testServer -Query "EXEC [master].[dbo].[xp_ss_restorefilelistonly] @filename = '$($fullBackup.physical_device_name)', @WindowsUsername = '$windowsUsername', @EncryptedWindowsPassword = '$windowsPassword'"

            # SQL Server standard        
            $fileList = Invoke-Sqlcmd2 -ServerInstance $testServer -Query "RESTORE FILELISTONLY FROM DISK = '$($fullBackup.physical_device_name)'"
            
			
			$dataFile = $fileList | Where-Object{$_.Type -eq "D"} 
			$logFile = $fileList | Where-Object{$_.Type -eq "L"} 
            
            # For Idera backups, specify a formatted parameter for data and log paths
			$dataMove = $($dataFile.LogicalName) + ' ' + $dataPath
			$logMove = $($logFile.LogicalName) + ' ' + $logPath

			# Idera
			# Note: This uses WITH REPLACE. Make sure this is what you want.
			<#
            $restoreQuery = "EXEC [master].[dbo].[xp_ss_restore]
                  @database = '$($fullBackup.database_name)'
    			, @filename = '$($fullBackup.physical_device_name)'
    			, @withmove = '$dataMove'
    			, @withmove = '$logMove'
    			, @Replace = 1 -- overwrite existing database 
    			, @DisconnectUsers = 1
    			, @WindowsUsername = '$windowsUsername'
    			, @EncryptedWindowsPassword = '$windowsPassword'
    			, @RecoveryMode = 'norecovery' -- may need to restore logs
    			, @nostatus = 0 -- send status to repository
    			, @ContinueAfterError = 0"
            #>
            
			# Note: This uses WITH REPLACE. Make sure this is what you want.
			$restoreQuery = "RESTORE DATABASE $($fullBackup.database_name) FROM DISK = '$($fullBackup.physical_device_name)' WITH REPLACE, NORECOVERY, CHECKSUM, MOVE '$($dataFile.LogicalName)' TO '$dataPath', MOVE '$($logFile.LogicalName)' TO '$logPath'"
            
			# Execute the restore query
			Write-Host "Restoring full backup for $($db.Name)";
			try
			{
				Invoke-Sqlcmd2 -ServerInstance $testserver -Database master -Query $restoreQuery -ErrorAction Stop

				if($logBackups -eq $null)
				{
                    $resultHTMLTable = $resultHTMLTable + "<td>No Log Backups</td>"
				} else
				{
                    $resultHTMLTable = $resultHTMLTable + "<td>Log Backups Exist</td>"
					foreach($log in $logBackups)
					{
						$restoreQuery = "RESTORE LOG $($db.Name) FROM DISK = '$($log.physical_device_name)' WITH NORECOVERY";

						Write-Host "Restoring log file $($log.physical_device_name) from $($log.backup_finish_date)";

						Invoke-Sqlcmd2 -ServerInstance $testserver -Database master -Query $restoreQuery -ErrorAction Stop
					}
				}

			}
			catch
			{
				$resultHTMLTable = $resultHTMLTable + "<td><font color=`"red`">$Error[0]</font></td>"

				# Recovering Database
				Invoke-Sqlcmd2 -ServerInstance $testServer -Query $recoverQuery -ErrorAction continue
                
				$Error.Clear();
			} 

			# Recovering Database
			Invoke-Sqlcmd2 -ServerInstance $testServer -Query $recoverQuery -ErrorAction continue
            
            # Drop the database
            Invoke-Sqlcmd2 -ServerInstance $testServer -Query $dropQuery -ErrorAction continue
            
			$resultHTMLTable = $resultHTMLTable + "<td>Success</td>"

			$resultHTMLTable = $resultHTMLTable + "</tr>"
		} # end if
	}
} 

$resultHTMLTable = $resultHTMLTable + "</table></body></html>";

# Send email report
Write-Host "Sending email";
Send-MailMessage -Subject "Database Restore Report" -BodyAsHtml $resultHTMLTable -SmtpServer $SMTPServer -To $EmailTo -From $EmailFrom