Use Powershell script to verify backup files

Before restoring backup, we always verify the backup file first, and run the 3 command below:

1. RESTORE HEADERONLY
    Returns a result set containing all the backup header information for all backup sets on a particular backup device.

2. RESTORE FILELISTONLY
    Returns a result set containing a list of the database and log files contained in the backup set.

3. RESTORE VERIFYONLY
    Verifies the backup but does not restore it, and checks to see that the backup set is complete and the entire backup is readable

sometimes we also want to keep that info for refererence later. If there is only one backup file, it is easy to handle. you can verify it from SSMS, or by sqlcmd. But if there are many backup files, it will be time consuming to verify them.

Here is a little Powershell Script which can help you verify the backup files, and export the results to file. For instance:
 in the folder below, we have 2 backup device, every backup device has 3 backup files



1. Run the powershell script first
2. Run verifyBackup following by the folder path, here is the sample:
verifyBackup "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\AWD"

then you will get the output like:

you can also get the detail report for each backup set in the same folder

some notes:
1. the current version only works on 1 backup set per device.
2. there are 3 parameters for verifyBackup function
         a) $backupPath: the bakcup file path
         b) $tempPath: the temp file path for "restore verifyonly". By default , it is same as $backupPath
         c) $sqlserver: by default, it use localhost as sql instance name

Powershell script:
##################################################################################
$global:backupsets = New-Object System.Collections.Hashtable
$global:restoreHeaderOnly = New-Object System.Collections.Hashtable
$global:restoreFilelistOnly = New-Object System.Collections.Hashtable
$global:restoreScripts= New-Object System.Collections.Hashtable
function global:run-sql (
 [String]$sql,
 [String]$server,
 [String]$database="master"
)
{
 $connectionString = "Server="+$server+";Database="+$database+";Trusted_Connection=yes"
 $conn = new-object System.Data.SqlClient.SqlConnection $connectionString
 $conn.Open()
 $comm = $conn.CreateCommand()
 $comm.CommandText = $sql
 $reader = $comm.ExecuteReader()
 while($reader.Read())
 {
  $row = new-object PSObject
  for($i = 0; $i -lt $reader.FieldCount; ++$i)
  {
   add-member -inputObject $row -memberType NoteProperty -name $reader.GetName($i) -value $reader.GetValue($i)
  }
  write-output $row
 }
 $reader.Close()
 $conn.Close()
}
function global:verifyBackup(
 [String]$backupPath,
 [String]$tempPath=$backupPath,
 [String]$sqlserver="localhost"
)
{
 $global:backupsets = New-Object System.Collections.Hashtable
 $global:restoreHeaderOnly = New-Object System.Collections.Hashtable
 $global:restoreFilelistOnly = New-Object System.Collections.Hashtable
 $global:restoreVerifyOnly = New-Object System.Collections.Hashtable
 $files=gci -Path $backupPath | where{$_.Extension -match "bak|trn"} | select name | sort-object name
 #Write-Output $files


 foreach($file in $files) {
  $filepath=$backupPath+"\" + $file.Name
  $sqlcmd="restore headeronly from disk='" + $filepath+"'"
  $headinfos=global:run-sql $sqlcmd $sqlserver

  foreach ($headinfo in $headinfos)
  {
   if ($headinfo){
    $key=$headinfo.DatabaseName #+"."+$headinfo.CheckpointLSN
    #Write-Output $key
    if ($global:backupsets.ContainsKey($key))
    {
     $global:backupsets.Item($key).add($filepath)
    }
    else
    {
     $temp=New-Object System.Collections.ArrayList
     $temp.add($filepath)
     $global:backupsets.Add($key, $temp)
    }
   }
  }
 }

 # run restore headeronly
 foreach ($fileKey in $global:backupsets.keys)

 {
  $sqlcmd="restore headeronly from "
  $backupfiles=$global:backupsets.Item($fileKey)
  foreach ($backupfile in $backupfiles)
  {
   $sqlcmd=$sqlcmd+"disk='"+$backupfile+"',"
  }
  if ($sqlcmd.EndsWith(","))
  {
   $sqlcmd=$sqlcmd.TrimEnd(",")
  }


  $restoreInfo=global:run-sql $sqlcmd $sqlserver
  $global:restoreHeaderOnly.Add($fileKey, $restoreInfo)

  $csvfile=$backupPath+"\"+$fileKey+"_headeronly.csv"
  $restoreInfo | Export-Csv -Path $csvfile

 }

 #run restore filelistonly
 foreach ($fileKey in $global:backupsets.keys)
 {
  $sqlcmd="RESTORE FILELISTONLY from "
  $backupfiles=$global:backupsets.Item($fileKey)
  foreach ($backupfile in $backupfiles)
  {
   $sqlcmd=$sqlcmd+"disk='"+$backupfile+"',"
  }
  if ($sqlcmd.EndsWith(","))
  {
   $sqlcmd=$sqlcmd.TrimEnd(",")
  }



  $restoreInfo=global:run-sql $sqlcmd $sqlserver
  $global:restoreFilelistOnly.Add($fileKey, $restoreInfo)

  $csvfile=$backupPath+"\"+$fileKey+"_filelistonly.csv"
  $restoreInfo | Export-Csv -Path $csvfile

 }

 #run restore verifyonly
 foreach ($fileKey in $global:backupsets.keys)
 {
  $sqlcmd="RESTORE VERIFYONLY from "
  $backupfiles=$global:backupsets.Item($fileKey)
  foreach ($backupfile in $backupfiles)
  {
   $sqlcmd=$sqlcmd+"disk='"+$backupfile+"',"
  }

  if ($sqlcmd.EndsWith(","))
  {
   $sqlcmd=$sqlcmd.TrimEnd(",")
  }

  $sqlcmd=$sqlcmd + " with "

  $fileList=$global:restoreFilelistOnly.Item($fileKey)

  foreach ($file in $fileList)
  {
   $logcalName=$file.LogicalName
   $physicalName=$file.PhysicalName | split-path -leaf
   $sqlcmd=$sqlcmd+ " move '"+$logcalName + "' to '" + $tempPath+"\"+ $physicalName+"',"
 
  }

  if ($sqlcmd.EndsWith(","))
  {
   $sqlcmd=$sqlcmd.TrimEnd(",")
  }
  $csvfile=$backupPath+"\"+$fileKey+"_verifyonly.txt"
  $cmd="sqlcmd -E -S " + $sqlserver + " -Q `" " + $sqlcmd + " `"  -o `""+ $csvfile+"`""
  invoke-expression $cmd
  $msg="Verify backup of database [" +$fileKey+ "] is done: `n"
  $msg+= gc $csvfile
  Write-Output $msg

  Add-Content $csvfile "`n"
  Add-Content $csvfile "$sqlcmd"
 }


}