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"
}
}