<# Last updated: 2014/06/18 Willem Gossink .SYNOPSIS Checks the integrity of SQL backup(s) through a restore and DBCC check. .DESCRIPTION This script is designed to check the integrity of SQL backup files by restoring them, preferably on another server, and submitting them to a quick DBCC check. In this way, you can ensure your backups are ready to be used when you really need them. In a nutshell, the script will query msdb backup history on a SQL server or a number of SQL servers and search for the most recent full and differential backup of each active database on the server(s). Next, it will attempt to restore each of these databases from the backup files found and submit it to a quick DBCC check. Database device locations are configurable. The restored database and file devices are given a (configurable) suffix. This allows you to restore a copy on the server hosting the original database, although this is not recommended from a performance perspective. Once the results of the restore and DBCC check have been reported, the restored database is again removed. You can run this solution manually or as a Windows Scheduled Task. Progress and any errors are reported in a log file and in a SQL log table. This script will restore from backup files accessible through a UNC path. SETUP INSTRUCTIONS On the server that runs this script: - Login with a Windows account that has access to the MSDB databases and the backup files of the SQL servers you are going to verify. - Set up a dedicated SQL instance (referred to as 'Restore Server' below) with sufficient disk space to contain a copy of your largest database (data, log and full-text indices). Data, log and full-text devices can be combined on one drive, or you can map each type to another drive. Make sure the SQL version of the Restore Server is at least identical to the highest SQL version used on the server(s) whose backups you want to check/restore. - In the SQL instance on your Restore Server, create a 5 MB database. Name it 'SQLRestorerDB' or any other name you specified for $SQLRestorerDB in the 'User Defined Parameters' below. Give the Windows account running this script dbo rights in this database (the database will contain a log table). - Copy this script to a dedicated directory on your Restore Server. For the purpose of this example the directory name 'D:\SQLRestorer' is used below. - Create a file (e.g. 'servers.txt') in the SQLRestorer directory. This file should contain the names of the SQL instance(s) whose backups you want to verify, one per line, unquoted. (For a named instance, use a backslash between server and instance name: server01\sql01). - If you wish to exclude certain databases, specify them in an execption file (e.g. 'exclusions.txt') in the SQLRestorer directory, one per line. Format: , . Do not use quotes. - Update the 'User Defined Parameters' in the section below to reflect the path and file names used for the server file and the optional exclusion file. Also, update the other User Defined Parameters to meet your local needs. - Make sure you have PowerShell version 2 or higher installed on your Restore Server. - Make sure you can load the SQLPS module in Powershell on your Restore Server. - In PowerShell make sure you can run external scripts, e.g. by issuing the command 'set-executionpolicy remotesigned -force', or by signing the script. Test your setup: ­ Change directory to the folder where the script resides; ­ Type its name preceded by '.\' and add the parameter 'DEBUG', for example: cd \SQLRestorer .\SQLRestorer.ps1 DEBUG ­ This will run the script in 'DEBUG' mode: it will write to the log file and log table, but not execute the actual restore(s) and DBCC check(s). ­ In DEBUG mode, the SQL commands for RESTORE and DBCC CHECK that would execute in regular mode are instead written to the log file for review. ­ Check the log file (defined above in 'User Defined Parameters') and the log table [SQLRestorerLog] for any anomalies. In the log file, problems are indicated by the word ‘ERROR’ at the start of a line. In the log table, errors have a value of ‘1’ in the [Error] column. ­ When all is OK, rerun the command without the 'DEBUG' parameter. The script will now perform the actual restores and checks. ­ After initialization, you should see database devices appearing in the file location(s) defined above and you should see the log file grow. - When the script is done, check its log file for the string ‘ERROR’. The SQL log table provides similar, but more detailed, information, so you can choose which source you prefer. .PARAMETER DEBUG (case insensitive, optional parameter, explained above) .EXAMPLE - From a PowerShell command line, run the script in debug mode: cd D:\SQLRestorer .\SQLRestorer.ps1 DEBUG - Run the script in regular mode: cd D:\SQLRestorer .\SQLRestorer.ps1 .NOTES Prerequisites: - Windows PowerShell version 2.0 or higher - SQLPS module. This is included with SQL2012. To obtain the module, check out http://sev17.com/2010/07/10/making-a-sqlps-module This script was tested against SQL 2012, SQL2008 (R2) and SQL2005 #> ### ===== USER DEFINED PARAMETERS ===== ### # SQL server instance name. Need not be changed if your Restore Server runs a default instance. # If you use a named SQL instance, supply name: '\' $SqlHost = $env:COMPUTERNAME # Path + name of log file. If you run the script as a Scheduled Task, # include the full path here or your log file will end up in the Windows System32 folder. $LogFile = 'D:\SQLRestorer\SQLRestorer_Log.txt' # Path + name of file with database exlusions. Optional, may be commented out. # Format of entries: , . ##$ExclusionFile = 'D:\SQLRestorer\exclusions.txt' # Path + name of file with server names whose SQL backups you want to restore and verify. $ServerFile = 'D:\SQLRestorer\servers.txt' # Name of database to be used by this script (will contain log table). # Database should exist prior to running this script. $SQLRestorerDB = 'SQLRestorerDB' # A suffix added to the (original) database name + physical database files during the restore, # to enable the script to restore to the same server (not recommended, but possible..) $Suffix = '_CHK' # Path to hold SQL data files of restored databases. Must exist when script starts. $NewDataPath = 'D:\SQL_DBS' # Path to hold SQL log files of restored databases. Must exist when script starts. $NewLogPath = 'D:\SQL_DBS' # Path to hold SQL full-text indices of restored databases. Must exist when script starts. $NewFtPath = 'D:\SQL_DBS\FT' # Number of days for which history is retained in the log table. $LogTableHistoryDays = 14 ### =========== FUNCTIONS ============ ### # === Function to check whether we can connect to a SQL server === function CheckServerConnection { Try { Invoke-Sqlcmd -ServerInstance ($Server.Name) -Query "SELECT @@SERVERNAME" -ConnectionTimeout 1 ` -ErrorAction SilentlyContinue return 1 } Catch { "ERROR: SQL connection to server '" +$Server.Name+ "' failed; server will be skipped. `r`n" ` | Out-File $LogFile -Append return -1 } } # === Function to check whether SQL on Restore Server has read access on backup file(s). Also collect database device files === function CheckSQLReadAccess { $FileListTable = Invoke-Sqlcmd -ServerInstance $SqlHost ` -Query "EXEC('RESTORE FILELISTONLY FROM DISK = ''$FullFile'' WITH FILE = $FullPos ')" 2>$null if ($FileListTable) { return 1 } else { return -1 } } # === Function to check read access on files other than SQL backup file(s) === function CheckReadAccess { $FileCheck = Get-Item -Path ${args}[0] 2>$null Try { if ($FileCheck.OpenRead().CanRead) { return 1 } } Catch { return -1 } } # === Function to check whether we have write access on a given path (used here for SQL DATA and LOG placement === function CheckSQLWriteAccess { Try { $SQL = "CREATE DATABASE [ThisDatabaseMayBeDeleted] ON (NAME = Data, FILENAME = '$NewDataPath\TDMBD.mdf', SIZE = 3), (NAME = FT, FILENAME = '$NewFTPath\TDMBD.ndf', SIZE = 1) LOG ON (NAME = Log, FILENAME = '$NewLogPath\TDMBD.ldf', SIZE = 1) ; " Invoke-Sqlcmd -ServerInstance "$SqlHost" -Query "$SQL" ` -ErrorVariable Err 2>&1 | out-null if ($Err) { $Txt = "ERROR: Unable to create test database on location(s) specified for " $Txt=$Txt+ "`$NewDataPath, `$NewLogPath or `$NewFTPath. `r`n" $Txt=$Txt+ "Please correct in the section 'User Definfed Variables'. Now exiting script." $Txt | Out-File $LogFile -Append $Err | Out-File $LogFile -Append break } } Finally { # Build command to drop database again $SQL = "IF (DB_ID('ThisDatabaseMayBeDeleted')) IS NOT NULL DROP DATABASE [ThisDatabaseMayBeDeleted] ; " # Drop the test database again Invoke-Sqlcmd -ServerInstance "$SqlHost" -Query "$SQL" -ErrorAction 'Stop' -OutputSqlErrors 1 2>&1 ` | Out-File $LogFile -Append } } # === Function to check whether the backup file in question was already successfully processed === # === (and logged in the SQLRestorerLog table) in an earlier session. First, check this for the === # === Differential backup, if one was found. Next, check this for the Full backup === function CheckIfNewFile { if ($Diff) { $SQL = "IF NOT EXISTS ( SELECT BackupFile FROM dbo.SQLRestorerLog WHERE BackupFile = '$DiffFile' AND Position = $DiffPos AND [Command] = 'Restore' AND [Error] = 0 ) SELECT '1'; " } else { $SQL = "IF NOT EXISTS ( SELECT BackupFile FROM dbo.SQLRestorerLog WHERE BackupFile = '$FullFile' AND Position = $FullPos AND [Command] = 'Restore' AND [Error] = 0 ) SELECT '1'; " } Invoke-Sqlcmd –ServerInstance $SqlHost -Database $SQLRestorerDB –Query "$SQL" } # === Function to verify free disk space for Data, Log and FullText devices === function CheckDiskSpace { # Record the values of the 3 input parameters this function should be supplied with, # i.e. the amount of space needed for SQL data device(s), log device(s) and fulltext device(s). $DataNeeded = ${args}[0] $LogNeeded = ${args}[1] $FtNeeded = ${args}[2] # Create an empty array. Populate with drive letters for data, log and fulltext devices, # derived from the user defined variables above. $Disks = @() $Disks += $NewDataPath.Substring(0,2) $Disks += $NewLogPath.Substring(0,2) $Disks += $NewFtPath.Substring(0,2) # Only store distinct drive letters $Disks = $Disks | Sort-Object -Unique # For each (distinct) disk in $Disks, determine current freespace in bytes and store in # array $DiskInfo (columns 'Disk' and 'Space') $DiskInfo = @() ForEach ($Disk in $Disks) { $DiskInfo += Get-WMIObject -Query "Select DeviceId,FreeSpace from Win32_LogicalDisk where ` DeviceId = '$Disk' " | Select-Object -property @{Name="Disk";Expression={$_.DeviceId}}, ` @{Name="Space";Expression={$_.FreeSpace}} } # Add to array $DiskInfo records indicating the bytes needed for the restore. # These values are recorded as negative numbers, for data, log and fulltext, respectively. # When we add the negative number to the freespace available, the freespace for that disk will # diminish. The values for $DataNeeded, $LogNeeded and $FtNeeded are calculated later in the script # (based on device size recorded in the backup file), and supplied when executing the function. $disk = New-Object System.Object $disk | Add-Member -type NoteProperty -name Disk -Value $NewDataPath.Substring(0,2) $disk | Add-Member -type NoteProperty -name Space -Value (-$DataNeeded) $DiskInfo += $disk $disk = New-Object System.Object $disk | Add-Member -type NoteProperty -name Disk -Value $NewLogPath.Substring(0,2) $disk | Add-Member -type NoteProperty -name Space -Value (-$LogNeeded) $DiskInfo += $disk $disk = New-Object System.Object $disk | Add-Member -type NoteProperty -name Disk -Value $NewFtPath.Substring(0,2) $disk | Add-Member -type NoteProperty -name Space -Value (-$FtNeeded) $DiskInfo += $disk # Sum the totals per disk (freespace + negative numbers needed for database devices). # If the total per disk is negative, add details to $Warning. If totals for all disks remain positive, # $Warning remains empty. $Warning='' ForEach($Disk in $Disks) { $BytesFree = ($DiskInfo | where {$_.Disk -eq $Disk } | Measure-Object Space -SUM | select -ExpandProperty SUM) if ($BytesFree -lt 0) { $Warning = $Warning + "ERROR: Disk: " + $Disk + " is " + (-$BytesFree) + " bytes short on diskspace. `r`n" } } $Warning } ### ========== START WORK ============= ### # === Set execution mode to 'Exec' unless 'Debug' was supplied as parameter. In 'Debug' mode the script does not === # === run the actual Restore and DBCC check. === $Mode = ${args}[0] if (! $Mode -eq "Debug") { $Mode = "Exec" } # === Load the sqlps module === Import-Module SQLPS -DisableNameChecking # === (Re)Initialize log file and update log file create time (column CreationTime is used for a check below). === # === Required if log file is recreated with identical name and automatic updates of 'lastaccesstime' is === # === disabled in Windows === "== Starting a new SQLRestorer session at " + (Get-Date -Format u) + "`r`n" | Out-File $LogFile Get-ChildItem $LogFile | Set-ItemProperty -Name CreationTime -Value (Get-ChildItem $LogFile).LastWriteTime # === Check read access on server file === $Check = CheckReadAccess $ServerFile # If server file is not readable, report problem and stop script if ($Check -eq -1) { "ERROR: == Problem reading server file; please correct and rerun script." | Out-File $LogFile -Append return } # === Check read access on exclusion file, if defined === if ($ExclusionFile) { $Check = CheckReadAccess $ExclusionFile # If exclusion file is not readable, report problem and stop script if ($Check -eq -1) { "ERROR: == Problem reading exclusion File; please correct and rerun script. " | Out-File $LogFile -Append return } } # Report that exclusion file is undefined else { "Exclusion File not defined, script will continue without exclusions" | Out-File $LogFile -Append } # === Create and delete a test database to check write accesss on directories for data, log and fulltext === # === devices for the account used by SQL server === # === Function will exit script if database creation fails === CheckSQLWriteAccess # === Create log table on RestoreServer if necessary === # === Else, cleanup log table === $SQL = @" IF OBJECT_ID('dbo.SQLRestorerLog') is NULL BEGIN CREATE TABLE dbo.SQLRestorerLog ( [ID] INT IDENTITY , [Date] DATETIME , [Server] SYSNAME NULL , [DB] SYSNAME NULL , [BackupFile] NVARCHAR(260) NULL , [Position] INT NULL , [Command] VARCHAR(20) NULL , [Error] BIT NULL , [Comments] VARCHAR(8000) NULL ) END ELSE BEGIN DELETE dbo.SQLRestorerLog WHERE [Date] < GETDATE() - $LogTableHistoryDays END ; "@ Invoke-Sqlcmd –ServerInstance $SqlHost -Database $SQLRestorerDB –Query "$SQL" 2>&1 | Out-File $LogFile -Append # === Record start of new session in log table === Invoke-Sqlcmd –ServerInstance $SqlHost -Database $SQLRestorerDB –Query "INSERT dbo.SQLRestorerLog ([Date], [Comments]) VALUES (GETDATE(),'Starting a new SQLRestorer session')" # === Retrieve names and SQL version info of servers supplying backups === # Record SQL version of Restore Server in variable $RestoreServerInfo = Invoke-Sqlcmd –ServerInstance $SqlHost –Query "SELECT SERVERPROPERTY('ProductVersion') AS 'ProductVersion' " $ServerInfo = @() # Read file with server names into variable. Delimiter is not used, but is specified to avoid a comma in a SQL # connection string with a port number to be used as separator $ServerList = Import-Csv -Delimiter "#" -Header Name $ServerFile ForEach ($Server in $ServerList | where {$_.Name -ne ''}) { $ConnectionCheck = CheckServerConnection "$Server.Name" if ($ConnectionCheck -eq 1) { # Record SQL version of each 'source' server in array $ServerInfo += Invoke-Sqlcmd –ServerInstance ($Server.Name) –Query "SELECT @@SERVERNAME AS 'ServerName', CONVERT(VARCHAR(128),SERVERPROPERTY('ProductVersion')) AS 'ProductVersion' " } } # === Check SQL versions and skip server if its version is higher than that of the Restore Server === $ServerList = @() ForEach ($Server in $ServerInfo) { # Add leading zero if ProductVersion starts with a single digit (SQL 2005 starts with '9', # SQL 2008 with '10') and strip last character ('0') to maintain equal length. if ($Server.ProductVersion.IndexOf(".") -eq 1) { $Server.ProductVersion = '0' + $Server.ProductVersion -replace ".$" } if ($Server.ProductVersion -gt $RestoreServerInfo.ProductVersion ) { # SQL version is higher than that of the Restore Server; do not add this server to the final list of servers $Txt= "ERROR: SQL Server version ("+$Server.ProductVersion+") of server "+$Server.ServerName+" " $Txt=$Txt+ "is higher than the SQL version ("+$RestoreServerInfo.ProductVersion+") of the restoring server. " $Txt=$Txt+ "This server will be skipped." $Txt | Out-File $LogFile -Append } # Version check is ok; add this server to the final list of servers in $ServerList else { $ServerList += $Server.ServerName } } # === Per server, query msdb backup history to determine last full and last differential backup per active database === # === Record results in $BackupInfoTMP === $BackupInfoTMP = @() ForEach ($Server in $ServerList) { $SQL = @" WITH OrderedBackups AS ( SELECT bs.server_name AS 'ServerName' , bs.[database_name] AS 'DBName' , CASE WHEN PATINDEX('[a-Z]:%',bmf.physical_device_name) > 0 -- replace local disk reference (e.g. 'd:') by UNC path (e.g. '\\\d$') -- to enable retrieval from Restore Server THEN '\\'+ bs.machine_name + '\'+REPLACE(bmf.physical_device_name,':','$') ELSE bmf.physical_device_name END AS 'BackupFile' , bs.last_lsn AS 'LastLSN' , bs.[type] AS 'Type' , bs.position AS 'Position' , ROW_NUMBER() OVER (PARTITION BY bs.database_name, bs.[type] ORDER BY bs.backup_finish_date DESC) AS 'Number' FROM msdb.dbo.backupset bs INNER JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id INNER JOIN master.sys.databases db ON bs.database_name = db.name WHERE db.[state] = 0 -- Only select active databases AND bs.is_copy_only = 0 -- Exclude backups made with 'copy_only' option AND PATINDEX('[a-Z\]%',bmf.physical_device_name) > 0 -- Regular disk file backups only AND bs.server_name = '$Server' AND bs.[type] in ('D', 'I') -- Full and Differential backups only AND bs.database_name NOT IN ('master') ) SELECT ServerName , DBName , BackupFile , LastLSN , [Type] , Position FROM OrderedBackups WHERE [Number] = 1 -- Last backup per DB and backup type "@ $BackupInfoTMP += Invoke-Sqlcmd -ServerInstance "$Server" -Query "$SQL" } # === Manage server and/or database exclusions === if ($ExclusionFile) { # Populate array of databases to be excluded $Exclusions = Import-Csv -Header ServerName, DbName $ExclusionFile $BackupInfo = @() # Loop through array with database backup info ForEach ($Db in $BackupInfoTMP) { # Reset variable for next server/db combination $Exclude = 0 # Loop through array with exclusions ForEach ($Exc in $Exclusions) { # If server name + database name appear in $Exclusions if ($Exc.ServerName -contains $Db.ServerName -and $Exc.DbName -contains $Db.DbName) { # Increase value of $Exclude to signal an exclusion $Exclude = $Exclude +1 } } if ($Exclude -eq 0) { # If $Exclude has remained unchanged, add entry to $BackupInfo, otherwise skip entry $BackupInfo += $Db } } } else { # No exclusions were defined, all backup info is incorporated $BackupInfo = $BackupInfoTMP } # === Loop through Full backups and populate variables with details needed for the restore === Clear-Variable -Name SQL ForEach ($Full in $BackupInfo | where {$_.type -eq 'D'}) { $ServerName = $Full.ServerName $DBName = $Full.DBName $DBNameNew = $DBName + $Suffix $FullFile = $Full.BackupFile $FullPos = $Full.Position $Diff = ($BackupInfo | where {$_.DBName -eq $DBName -and $_.ServerName -eq $ServerName -and $_.type -eq 'I' ` -and $_.LastLSN -ge $Full.LastLsn}) $DiffFile = $Diff.BackupFile $DiffPos = $Diff.Position # === Check if backup file(s) was not processed in an earlier run === $CheckIfNewFile = CheckIfNewFile # If file is new, continue if ($CheckIfNewFile) { # Check read access from within SQL for full backup file. $Check = . CheckSQLReadAccess $FullFile # Retain return value for access to full backup file for error logging purposes $Check_F = $Check if ($Diff) { # Check read access from within SQL for diff backup file $Check = $Check + (CheckSQLReadAccess $DiffFile) } # For each file, if read access is ok, function returns 1, else -1. # Hence, final return value for an 'ok' should always be > 0 # (Check on 2 files: both not ok = -2, one ok = 0, both ok = 2) # (Check on 1 file: not ok = -1, ok = 1) if ($Check -gt 0) { # === Log results of access checks === Invoke-Sqlcmd -ServerInstance $SqlHost -Database $SQLRestorerDB -Query "INSERT dbo.SQLRestorerLog VALUES ` (GETDATE(), '$ServerName', '$DBName', '$FullFile', NULL, 'Check read access', 0, NULL)" "== Server: " +$ServerName+ ", database: " +$DBName+ ", backup file: "+$FullFile+", read access OK." ` | Out-File $LogFile -Append if ($Diff -and ($FullFile -ne $DiffFile)) { Invoke-Sqlcmd -ServerInstance $SqlHost -Database $SQLRestorerDB -Query "INSERT dbo.SQLRestorerLog VALUES ` (GETDATE(), '$ServerName', '$DBName', '$DiffFile', NULL, 'Check read access', 0, NULL)" "== Server: " +$ServerName+ ", database: " +$DBName+ ", backup file: "+$DiffFile+", read access OK." ` | Out-File $LogFile -Append } # === Check if sufficient disk space is available for a restore of this database === # Sum (device) 'Size' for data device(s) $DataNeeded = ($FileListTable | where {$_.Type -eq 'D'} | Measure-Object Size -sum).sum # Sum (device) 'Size' for log device(s) $LogNeeded = ($FileListTable | where {$_.Type -eq 'L'} | Measure-Object Size -sum).sum # Sum (device) 'Size' for Full-Text indices $FtNeeded = ($FileListTable | where {$_.Type -eq 'F'} | Measure-Object Size -sum).sum # Call function 'CheckDiskSpace' to determine if enough freespace is available $DiskSpaceShort = CheckDiskSpace $DataNeeded $LogNeeded $FtNeeded if (! $DiskSpaceShort) { # === If disk space is ok, build command to restore database and move devices to new location === $SQL = "RESTORE DATABASE [$DBNameNew] FROM DISK = '$FullFile' WITH FILE = $FullPos , " # Loop through database devices and create 'MOVE' statement for each device for ($i=0;$i -le $FileListTable.GetUpperBound(0);$i ++) { if ($FileListTable[$i].Type -eq 'D') {$Path = $NewDataPath + "\"} elseif ($FileListTable[$i].Type -eq 'L') {$Path = $NewLogPath + "\"} else {$Path = $NewFtPath + "\"} ; $SQL=$SQL+ "MOVE '"+$FileListTable[$i].LogicalName +"' TO '" + $Path ` + [io.path]::GetFileName($FileListTable[$i].PhysicalName) + $Suffix +"', " } $SQL=$SQL + " NORECOVERY ;" if ($Diff) { # Add statement to restore differential backup if applicable $SQL=$SQL + "RESTORE DATABASE [$DBNameNew] FROM DISK = '$DiffFile' WITH FILE = $DiffPos , ` NORECOVERY ;" } # Add statement to recover the database $SQL=$SQL + "RESTORE DATABASE [$DBNameNew] WITH RECOVERY;" # === Run restore + DBCC check if script is in 'Exec' mode, else just log the SQL command(s) === Try { # Perform the restore operation if ($Mode -eq "Exec") { Invoke-Sqlcmd -QueryTimeout 65535 -ServerInstance $SqlHost -Query "$SQL" -ErrorAction 'Stop' ` -OutputSqlErrors 1 2>&1 | Out-File $LogFile -Append } else { # Only log the commands $SQL | Out-File $LogFile -Append } # Log results for restore if ($Mode -eq "Exec") { Invoke-Sqlcmd -ServerInstance $SqlHost -Database $SQLRestorerDB -Query "INSERT dbo.SQLRestorerLog VALUES ` (GETDATE(), '$ServerName', '$DBName', '$FullFile', $FullPos, 'Restore', 0, NULL)" if ($Diff) { Invoke-Sqlcmd -ServerInstance $SqlHost -Database $SQLRestorerDB -Query "INSERT dbo.SQLRestorerLog VALUES ` (GETDATE(), '$ServerName', '$DBName', '$DiffFile', $DiffPos, 'Restore', 0, NULL)" } "== Server: "+$ServerName+", database: "+$DBName+", Restore OK" | Out-File $LogFile -Append } # Build command for DBCC check $SQL = "DBCC CHECKDB ([$DBNameNew]) WITH PHYSICAL_ONLY ;" # Perform the DBCC check if ($Mode -eq "Exec") { Invoke-Sqlcmd -QueryTimeout 65535 -ServerInstance $SqlHost -Query "$SQL" -ErrorAction 'Stop' ` -OutputSqlErrors 1 2>&1 | Out-File $LogFile -Append } # Only log the commands else { $SQL | Out-File $LogFile -Append } # Log record/log entry for DBCC check Invoke-Sqlcmd -ServerInstance $SqlHost -Database $SQLRestorerDB -Query "INSERT dbo.SQLRestorerLog ` VALUES (GETDATE(), '$ServerName', '$DBName', NULL, NULL, 'DBCC check', 0, NULL)" "== Server: " +$ServerName+ ", database: " +$DBName+ ", DBCC check OK." ` | Out-File $LogFile -Append } # === Log errors === Catch { "ERROR: == Server: " +$ServerName+ ", database: " +$DBName+ ", Restore/DBCC check failed." ` | Out-File $LogFile -Append "== SQL command used: " | Out-File $LogFile -Append $SQL | Out-File $LogFile -Append "== Error message(s): " | Out-File $LogFile -Append $_ | Out-File $LogFile -Append Invoke-Sqlcmd -ServerInstance $SqlHost -Database $SQLRestorerDB -Query "INSERT dbo.SQLRestorerLog VALUES ` (GETDATE(), '$ServerName', '$DBName', NULL, NULL, 'Restore/DBCC', 1, 'ERROR in restore or DBCC check:` please check error file ''$LogFile'' on server ''$env:COMPUTERNAME'' for more details.')" } # === Cleanup: remove database === Finally { # Determine when Log file was created $Start = Get-ChildItem $LogFile | Select-Object CreationTime -ExpandProperty CreationTime # Build command to drop restored database if it was created during the current session of this script $SQL = "IF (DB_ID('$DBNameNew')) IS NOT NULL AND (SELECT create_date FROM sys.databases WHERE database_id = DB_ID('$DBNameNew')) > '$Start' DROP DATABASE [$DBNameNew] ; " ; # Run the 'Drop Database' command if script is in 'Exec' mode, else just log the SQL command(s) if ($Mode -eq "Exec") { Invoke-Sqlcmd -ServerInstance $SqlHost -Database master -Query "$SQL" -ErrorAction 'Stop' ` -OutputSqlErrors 1 2>&1 | Out-File $LogFile -Append } else { $SQL | Out-File $LogFile -Append } } } # === Exception logging below. For each problem, write error details to log file and create log record === # # Insufficient disk space. Log details on extra drive space needed else { $Txt= "ERROR: == Server: " +$ServerName+ ", database: " +$DBName+ ", insufficient free disk " $Txt=$Txt+ "space for restore of this database." $Txt | Out-File $LogFile -Append $DiskSpaceShort | Out-File $LogFile -Append Invoke-Sqlcmd -ServerInstance $SqlHost -Database $SQLRestorerDB -Query "INSERT dbo.SQLRestorerLog ` VALUES (GETDATE(), '$ServerName', '$DBName', NULL, NULL, 'Check disk space', 1, ` 'Insufficient free disk space for restore of this database: please check error file ''$LogFile'' ` on server ''$env:COMPUTERNAME'' for more details.')" } } else { # Read access failure on full backup file if ($Check_F -eq -1) { "ERROR: == Read access on backup file '"+$FullFile+"' failed. This restore will be skipped." ` | Out-File $LogFile -Append Invoke-Sqlcmd -ServerInstance $SqlHost -Database $SQLRestorerDB -Query "INSERT dbo.SQLRestorerLog ` VALUES (GETDATE(), '$ServerName', '$DBName', '$FullFile', NULL, 'Check read access', 1, ` 'Read access on backup file failed. This restore will be skipped.')" } # Read access failure on diff backup file else { "ERROR: == Read access on backup file '"+$DiffFile+"' failed. This restore will be skipped." ` | Out-File $LogFile -Append Invoke-Sqlcmd -ServerInstance $SqlHost -Database $SQLRestorerDB -Query "INSERT dbo.SQLRestorerLog ` VALUES (GETDATE(), '$ServerName', '$DBName', '$DiffFile', NULL, 'Check read access', 1, ` 'Read access on backup file failed. This restore will be skipped.')" } } } else { # Diff backup file has already been processed if ($Diff) { $Txt= "ERROR: == Backup file '" +$DiffFile+ "' was already restored in an earlier session. " $Txt=$Txt+ "This restore will be skipped." $Txt | Out-File $LogFile -Append Invoke-Sqlcmd -ServerInstance $SqlHost -Database $SQLRestorerDB -Query "INSERT dbo.SQLRestorerLog ` VALUES (GETDATE(), '$ServerName', '$DBName', '$DiffFile', $DiffPos, 'Duplicate check', 1, ` 'Diff backup file was already restored in an earlier session. This restore will be skipped.')" } # Full backup file has already been processed else { $Txt= "ERROR: == Backup file '" +$FullFile+ "' was already restored in an earlier session. " $Txt=$Txt+ "This restore will be skipped." $Txt | Out-File $LogFile -Append Invoke-Sqlcmd -ServerInstance $SqlHost -Database $SQLRestorerDB -Query "INSERT dbo.SQLRestorerLog ` VALUES (GETDATE(), '$ServerName', '$DBName', '$FullFile', $FullPos, 'Duplicate check', 1, ` 'Full backup file was already restored in an earlier session. This restore will be skipped.')" } } } # === Record end of this session in log table === "`r`n== Ending the SQLRestorer session at " + (Get-Date -format u) +"`r`n" | Out-File $LogFile -Append Invoke-Sqlcmd –ServerInstance $SqlHost -Database $SQLRestorerDB –Query "INSERT dbo.SQLRestorerLog ([Date], [Comments]) ` VALUES (GETDATE(),'Ending the current SQLRestorer session')"