Verify SQL Backup Integrity

  • Willem G

    SSC Eights!

    Points: 809

    Comments posted to this topic are about the item Verify SQL Backup Integrity

  • jaynsh

    Grasshopper

    Points: 20

    Hi,

    Thanks for your great scripts and I am sure this would be quite helpful to most of the members.

    In my environnent, we have setup TDP to backup to tapes directly, so can I sue this script to retrieve directly from TSM server ?? If Yes, please mention any changes I need to carry out.

    Again sincere thanks !!!

  • david.wright-948385

    SSCarpal Tunnel

    Points: 4028

    We have a very similar setup, which works very well and has saved us from catastrophe a few times now.

  • jaynsh

    Grasshopper

    Points: 20

    hello,

    Currently, we have SQL Server 2008 R2 and when we run script, it gives error "SQLPS could not be found". So my question is :

    1) Does this script works only for local DB restore or also for remote server DB also ??

    2) How can we fix SQLPS issues on SQL Server 2008 ? Since it is not preloaded in this version.

  • SQLnbe

    Mr or Mrs. 500

    Points: 521

    Great concept, will give it a shot sometime.

  • Willem G

    SSC Eights!

    Points: 809

    I am away on holidays and will reply in approx 2 weeks time.

  • Willem G

    SSC Eights!

    Points: 809

    jaynsh (8/11/2014)


    Hi,

    Thanks for your great scripts and I am sure this would be quite helpful to most of the members.

    In my environnent, we have setup TDP to backup to tapes directly, so can I sue this script to retrieve directly from TSM server ?? If Yes, please mention any changes I need to carry out.

    Again sincere thanks !!!

    I am not familar with TDP. If the backup is logged in msbdb.dbo.backupset + msdb.dbo.backupmediafamily and you can restore a backup made with TDP from a TSQL command line, it should work. So if you can do sth like this:

    restore database xx from 'TDP backup location' with <sql parameters>

    it could be made to work.

    You would need to edit the SQL select command in the section labelled:

    # === Per server, query msdb backup history to determine last full and last differential backup per active database ===

    This is used to locate the relevant backups. But you would also need to look into the function that checks read access on backup files and check the restore command buildup.

    I hope this will help you!

  • Willem G

    SSC Eights!

    Points: 809

    jaynsh (8/11/2014)


    hello,

    Currently, we have SQL Server 2008 R2 and when we run script, it gives error "SQLPS could not be found". So my question is :

    1) Does this script works only for local DB restore or also for remote server DB also ??

    2) How can we fix SQLPS issues on SQL Server 2008 ? Since it is not preloaded in this version.

    Hi,

    1) is explained in the article: works for both

    2) Either use SQL 2012 Express on Restore Server or check out URL specified in the article

  • WJBergen

    SSC Rookie

    Points: 45

    Great script...But

    not being a ps expert I am having trouble getting it to run on a named instance....followed your doc to the letter and basically nothing works on 2008R2 with SP2....any chance of a fast fix if that is the problem.

    Also, script never asks (as far as I can tell) for the remote server name (name and instance) to query msdb for most recent backup....so how does this script go to an unidentified server\instance and copy the backup files to the local server where this script is installed

    Thanks in advance and keep the great work woming.....


    Bill Bergen

  • Willem G

    SSC Eights!

    Points: 809

    WJBergen (9/2/2014)


    Great script...But

    not being a ps expert I am having trouble getting it to run on a named instance....followed your doc to the letter and basically nothing works on 2008R2 with SP2....any chance of a fast fix if that is the problem.

    Also, script never asks (as far as I can tell) for the remote server name (name and instance) to query msdb for most recent backup....so how does this script go to an unidentified server\instance and copy the backup files to the local server where this script is installed

    Thanks in advance and keep the great work woming.....

    Hi Bill,

    I am sorry to hear you could not get it to work. I run 2008R2 SP2, so that should not be the problem.

    Server and instance names are recorded in the section that starts with

    # === Retrieve names and SQL version info of servers supplying backups ===

    (they end up in the variable $ServerList). Do you get any (error) messages at all?

    I would be glad to help you. Perhaps you can send me an email through sqlservercentral and we could be in touch to see if I can help you out.

    To send a mail:

    Click 'Authors' on the sqlservercentral.com homepage.

    Next, click on my name (Willem Gossink) in the list.

    On the next page click on 'SQLServerCentral.com profile' and in the top-left box called 'Contact Information', under 'Public Email',

    click on 'Send this member a private message'.

    Willem

  • WJBergen

    SSC Rookie

    Points: 45

    Sorry for the incredibly long delay in responding....been lost in other work.....have included the slightly modified (only the parameters discussed in the script....could you send me an email like (william.bergen@uphs.upenn.edu) do make communication easier.....thanks in advance.....

    <#

    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: <SQL instance>, <database name>. 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: '<server name>\<sql instance name>'

    #$SqlHost = $env:'DBAWINDOWSBACKUPTEST\SQLBACKUPTST'

    $SqlHost = 'DBAWINDOWSBACKUPTEST\SQLBACKUPTST'

    # 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 = 'E:\SQLRESTORER\SQLRestorer_Log.txt'

    #$LogFile = '.\SQLRestorer_Log.txt'

    # Path + name of file with database exlusions. Optional, may be commented out.

    # Format of entries: <server_name>, <db_name>.

    $ExclusionFile = 'E:\SQLRESTORER\exclusions.txt'

    # Path + name of file with server names whose SQL backups you want to restore and verify.

    $ServerFile = 'E:\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 = 'E:\Microsoft SQL Server\MSSQL11.SQLBACKUPTST\MSSQL\DATA'

    $NewDataPath = 'E:\Microsoft SQL Server\MSSQL11.SQLBACKUPTST\MSSQL\DATA'

    # Path to hold SQL log files of restored databases. Must exist when script starts.

    #$NewLogPath = 'E:\Microsoft SQL Server\MSSQL11.SQLBACKUPTST\MSSQL\DATA'

    $NewLogPath = 'E:\Microsoft SQL Server\MSSQL11.SQLBACKUPTST\MSSQL\DATA'

    # Path to hold SQL full-text indices of restored databases. Must exist when script starts.

    #$NewFtPath = 'E:\SQLRESTORER\sQL_DBS'

    $NewFtPath = '\\DBAWINDOWSBACKUPTEST\E:\SQLRESTORER\sQL_DBS'

    # 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. '\\<server>\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')"


    Bill Bergen

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply