Verify SQL Backups With SQL Powershell

Question

Verify SQL Backups With SQL Powershell

GeoffRuble-976616

Old Hand

Points: 378
More actions
April 24, 2016 at 11:02 pm

#325878

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

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

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

sjones-143898 SSC Rookie Points: 36 More actions · Answer 1

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 More actions · Answer 2

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: 38 More actions · Answer 3

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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 4

akljfhnlaflkj

SSC Guru

Points: 76202

April 25, 2016 at 6:49 am

#1874205

Thanks for the article.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

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.

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

GeoffRuble-976616 Old Hand Points: 378 More actions · Answer 6

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 More actions · Answer 7

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 More actions · Answer 8

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 More actions · Answer 9

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

YOUKHANIS.JAMES SSC Enthusiast Points: 156 More actions · Answer 10

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 More actions · Answer 11

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 More actions · Answer 12

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: 1004406 More actions · Answer 13

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.

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

bnosrati SSC Rookie Points: 25 More actions · Answer 14

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!