Verify SQL Backups With SQL Powershell

  • GeoffRuble-976616

    Old Hand

    Points: 378

    Comments posted to this topic are about the item Verify SQL Backups With SQL Powershell

  • sjones-143898

    SSC Rookie

    Points: 36

    Looks very useful- however the icons for the download scripts do not seem to have any links for the download neither in the post, nor in the docx file.

  • Chris Miller-292763

    Valued Member

    Points: 54

    Hi Geoff, thanks for sharing this article... based on the title I was hoping that it would solve something that I'm currently working on and that is to copy the backups (full and TL backups) to a staging server and perform a restore of these saving the status of the restore and generating a report. Have you come across anything like this?

    Thanks,

    Chris.

  • backintheday

    SSC Rookie

    Points: 28

    To echo a previous post, the icons of the scripts and functions at not linked to any source.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the article.

  • Jeff Moden

    SSC Guru

    Points: 997113

    I'm sometimes torn between centralized and distributed systems. While I love the convenience of centralized monitoring systems, if something goes haywire with them, then you're monitoring nothing or not monitoring what you think you're monitoring.

    I do appreciate the effort that went into making this article, though. Couldn't rate it because, at the time that I read it, the links weren't working.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • GeoffRuble-976616

    Old Hand

    Points: 378

    Not sure how this could have happened. I have reached out to the webmaster to see if we can get those links working.

  • YOUKHANIS.JAMES

    SSC Enthusiast

    Points: 156

    Hello Geoff,

    First and Foremost thank you for all your hard work on sharing this blog with us. My question is where can I download the CheckBackups.ps1 PowerShell script from? Thank you and keep up the great articles.

  • GeoffRuble-976616

    Old Hand

    Points: 378

    Until we get the links working in this article, here they are in order, separated by line breaks.

    ------------------------------------------------------------------------------------------------------------------

    Here is the Backups.sql script written by Tim Radney which I modified slightly...

    /*

    I used this insert column by column to test the datatypes. I was getting a lot of errors tyring to insert.

    INSERT INTO Backups

    (DatabaseName, RecoveryModel, LastFullBackup, LastDiffBackup, LastTranLogBackup, [2ndToLastTranLogBackup],

    CheckDate, DaysSinceLastBackup, MachineName, ServerName, InstanceName, ComputerNamePhysicalNetBIOS)

    */

    SELECT DISTINCT

    a.Name AS DatabaseName ,

    CONVERT(SYSNAME, DATABASEPROPERTYEX(a.name, 'Recovery')) RecoveryModel ,

    COALESCE(( SELECT CONVERT(VARCHAR(12), MAX(backup_finish_date), 101)

    FROM msdb.dbo.backupset

    WHERE database_name = a.name

    AND type = 'd'

    AND is_copy_only = '0'

    ), 'No Full') AS 'Full' ,

    COALESCE(( SELECT CONVERT(VARCHAR(12), MAX(backup_finish_date), 101)

    FROM msdb.dbo.backupset

    WHERE database_name = a.name

    AND type = 'i'

    AND is_copy_only = '0'

    ), 'No Diff') AS 'Diff' ,

    COALESCE(( SELECT CONVERT(VARCHAR(20), MAX(backup_finish_date), 120)

    FROM msdb.dbo.backupset

    WHERE database_name = a.name

    AND type = 'l'

    ), 'No Log') AS 'LastLog' ,

    COALESCE(( SELECT CONVERT(VARCHAR(20), backup_finish_date, 120)

    FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY backup_finish_date DESC ) AS 'rownum' ,

    backup_finish_date

    FROM msdb.dbo.backupset

    WHERE database_name = a.name

    AND type = 'l'

    ) withrownum

    WHERE rownum = 2

    ), 'No Log') AS 'LastLog2',

    GETDATE() AS [CheckDate],

    ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,

    SERVERPROPERTY('MachineName') AS [MachineName] ,

    SERVERPROPERTY('ServerName') AS [ServerName],

    SERVERPROPERTY('InstanceName') AS [InstanceName],

    SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS]

    FROM sys.databases a

    LEFT OUTER JOIN msdb.dbo.backupset b ON B.database_name = A.name

    WHERE a.name <> 'tempdb'

    AND B.type = 'D'

    AND a.state_desc = 'online'

    GROUP BY A.Name

    ORDER BY A.name

    ---------------------------------------------------------------------------------------------------------------

    Here are links to the four functions:

    Invoke-sqlcmd2.ps1: http://gallery.technet.microsoft.com/ScriptCenter/en-us/7985b7ef-ed89-4dfd-b02a-433cc4e30894

    Out-DataTable.ps1: http://gallery.technet.microsoft.com/ScriptCenter/en-us/4208a159-a52e-4b99-83d4-8048468d29dd

    Write-datatable.ps1: http://gallery.technet.microsoft.com/ScriptCenter/en-us/2fdeaf8d-b164-411c-9483-99413d6053ae

    LoggingFunction.ps1: http://9to5it.com/powershell-logging-function-library/

    -------------------------------------------------------------------------------------------------------------------

    Here is the checkbackups.ps1 script:

    #requires -version 2

    <#

    .SYNOPSIS

    This script makes a connection to my SQL Server list and pulls back my list of servers I want to gather info on. It will then

    run a SQL script which queries the system tables for backup information, then it will upload that information into a central SQL

    table which we can query and report off of.

    .NOTES

    Version: 1.0

    Author: Geoff Ruble

    Creation Date: 8/4/2015

    Purpose/Change: Check Backup Information on our SQL Servers.

    http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/01/use-powershell-to-collect-server-data-and-write-to-sql.aspx

    http://9to5it.com/powershell-script-template/

    .EXAMPLE

    NA

    #>

    #---------------------------------------------------------[Initialisations]--------------------------------------------------------

    $password=$args[0] #this allows us to pass the $password variable through the Blade Logic batch file so it's hidden and ecrypted.

    #Set Error Action to Silently Continue

    $ErrorActionPreference = "SilentlyContinue"

    #Dot Source required Function Libraries

    . D:\geoff\PowerShellFunctions\invoke-sqlcmd2.ps1 # this sources a more robust version of "invoke-sql" so we can run SQL commands

    . D:\geoff\PowerShellFunctions\out-datatable.ps1 # allows us to take the output of a powershell WMI call into an in memory table.

    . D:\geoff\PowerShellFunctions\write-datatable.ps1 # allows us to load the in memory table into the SQL table.

    . D:\geoff\PowerShellFunctions\LoggingFunction.ps1 # allows us to log

    #----------------------------------------------------------[Declarations]----------------------------------------------------------

    #Script Version

    $sScriptVersion = "1.0"

    #Log File Info

    $sLogPath = "D:\geoff\PowerShellLogs"

    $sLogName = "CheckBackups$(Get-Date -f _yyyy_MM_dd_hhmm).log"

    $sLogFile = Join-Path -Path $sLogPath -ChildPath $sLogName

    Log-Start -LogPath $sLogPath -LogName $sLogName -ScriptVersion $sScriptVersion

    #------------------------------------ Getting Backup Info for each server in serverlist ------------------------------------

    $Error.Clear() #this will clear any errors stored in the error variable

    Clear-Variable sError #this will clear any errors stored in the error variable

    Log-Write -LogPath $sLogFile -LineValue "

    Getting list of SQL Servers to gather SQL Backup Information...

    "

    $DT_Backups = `

    Invoke-sqlcmd2 `

    -ServerInstance "sqlserver01\sql2014,1433" `

    -Database DBAUtil `

    -Query "SELECT TOP 2 SQLConnectionString FROM ServerList WHERE IsActive = '1' and Environment = 'test' " |

    `

    foreach-object{

    $a = $_.SQLConnectionString

    Log-Write -LogPath $sLogFile -LineValue "Gathering historical SQL backup information on $a..."

    `

    Invoke-SqlCmd2 `

    -ServerInstance $_.SQLConnectionString `

    -Database master `

    -Username UserName `

    -Password $password `

    -InputFile D:\geoff\SQLScripts4PowerShell\backups.sql

    $sError = $error[1]

    Log-Error -LogPath $sLogFile -ErrorDesc $sError -ExitGracefully $False

    $Error.Clear()

    Clear-Variable sError

    } |out-datatable #this will load the table into an in memory table variable, which we can call later in the script to load into a real SQL table

    Log-Write -LogPath $sLogFile -LineValue "

    All information has been gathered...

    Uploading historical backup data to sqlserver01\sql2014,1433.DBAUtil.Backups...

    "

    #This will upload the in memory table into a SQL table. This table will need to exist and have appropriate clumns/datatypes.

    Write-DataTable `

    -ServerInstance "sqlserver01\sql2014,1433" `

    -Database "DBAUtil" `

    -TableName "Backups" `

    -Data $DT_Backups

    Log-Finish -LogPath $sLogFile

  • sjones-143898

    SSC Rookie

    Points: 36

    Thank you for posting the scripts - that will do nicely.

  • YOUKHANIS.JAMES

    SSC Enthusiast

    Points: 156

    Hello Geoff, Thank you for the scripts, however when I run the checkbackups.ps1 script nothing is logged and also no data is captured. I am using windows authentication, so i commented out username and password. Can you shed some light on possible issues.

    Thanks

  • Ness

    SSCarpal Tunnel

    Points: 4282

    Nice article although I think "Verify SQL backups" is misleading; there is nothing verifying the backups, just checking that they ran and completed. You would need to restore and integrity check the backups to 'verify'. Perhaps Monitoring SQL Backups would be a better heading.

    SQL DBA
    Every day is a school day, and don't trust anyone who tells you any different.
    http://sqlblogness.blogspot.co.uk

  • YOUKHANIS.JAMES

    SSC Enthusiast

    Points: 156

    What TSQL statement populates this table?

    SELECT [MachineName]

    ,[AssociatedApp]

    ,[App_ID]

    ,[SQLConnectionString]

    ,[InstanceName]

    ,[IsActive]

    ,[Domain]

    ,[Environment]

    ,[PDBA]

    ,[SDBA]

    FROM [DBA].[dbo].[ServerList]

  • Jeff Moden

    SSC Guru

    Points: 997113

    YOUKHANIS.JAMES (4/26/2016)


    What TSQL statement populates this table?

    SELECT [MachineName]

    ,[AssociatedApp]

    ,[App_ID]

    ,[SQLConnectionString]

    ,[InstanceName]

    ,[IsActive]

    ,[Domain]

    ,[Environment]

    ,[PDBA]

    ,[SDBA]

    FROM [DBA].[dbo].[ServerList]

    Manual INSERTs.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • bnosrati

    SSC Rookie

    Points: 25

    YOUKHANIS.JAMES (4/26/2016)


    Hello Geoff, Thank you for the scripts, however when I run the checkbackups.ps1 script nothing is logged and also no data is captured. I am using windows authentication, so i commented out username and password. Can you shed some light on possible issues.

    Thanks

    I am having the same issue as well. It appears to execute but nothing actually occurs.

    Update:

    I changed CheckBackups.ps1 line 26 to "Continue" and I am now able to view the errors. It seems that there are some formatting issues with the additional .ps1 files.

    For example, invoke-sqlcmd2.ps1 was missing } at the end of the script which was causing it to fail.

    Hopefully this information is useful!

Viewing 15 posts - 1 through 15 (of 22 total)

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