{"id":246,"date":"2013-05-03T17:01:00","date_gmt":"2013-05-03T15:01:00","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=246"},"modified":"2013-11-11T11:07:52","modified_gmt":"2013-11-11T09:07:52","slug":"sqlserver-centralized-backup-monitoring-with-powershell-and-tsql-12","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/sqlserver-centralized-backup-monitoring-with-powershell-and-tsql-12\/","title":{"rendered":"SQLServer centralized backup monitoring with PowerShell and TSQL (1\/2)"},"content":{"rendered":"<p><a href=\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2013\/05\/Monitoring_a_simulated_test_at_Central_Control_Facility_at_Eglin_Air_Force_Base_080416-F-5297K-101.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-248 alignleft\" title=\"U.S. Air Force: public domain image\" alt=\"U.S. Air Force: public domain image\" src=\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2013\/05\/Monitoring_a_simulated_test_at_Central_Control_Facility_at_Eglin_Air_Force_Base_080416-F-5297K-101.jpg\" width=\"256\" height=\"137\" \/><\/a>Checking database backups has always been one of the main concerns of DBAs. With Oracle is quite easy with a central RMAN catalog, but with other databases\u00a0doing it with few effort can be a great challenge.<\/p>\n<p>Some years ago I developed a little framework to control all SQLServer databases. This framework was based on Linux (strange but true!), bash, freetds, sqsh and flat configuration files. It&#8217;s still doing well its work, but not all SQLServer DBAs can deal with complex bash scripting, so a customer of mines asked me if I was able to rewrite it with a language Microsoft-like.<\/p>\n<p>So I decided to go for a PowerShell script in conjunction with a couple of tables for the configuration and the data, and a simple TSQL script to provide HTML reporting. I have to say, I&#8217;m not an expert on PowerShell, but it&#8217;s far from being as flexible as other programming languages (damn, comparing to perl, python or php they have in common only the initial &#8216;P&#8217;). However I managed to do something usable.<\/p>\n<p><strong>The principle<\/strong><\/p>\n<p>This is quite simple: the PowerShell script looks up for the list of instance in a reference table, then it sequentially connect to and retrieves the data:<\/p>\n<ul>\n<li>recovery mode<\/li>\n<li>status<\/li>\n<li>creation time<\/li>\n<li>last full backup<\/li>\n<li>last log backup<\/li>\n<\/ul>\n<p>This data is merged into a table on the central repository. Finally, a TSQL script do some reporting.<\/p>\n<p><a href=\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2013\/05\/sql_centralized_backup_monitor_schema.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-250\" alt=\"sql_centralized_backup_monitor_schema\" src=\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2013\/05\/sql_centralized_backup_monitor_schema.png\" width=\"475\" height=\"226\" srcset=\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2013\/05\/sql_centralized_backup_monitor_schema.png 475w, https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2013\/05\/sql_centralized_backup_monitor_schema-300x142.png 300w\" sizes=\"auto, (max-width: 475px) 100vw, 475px\" \/><\/a><\/p>\n<p><strong>Custom classes in powershell<\/strong><\/p>\n<p>One of the big messes with PowerShell is the lack of the definition for custom classes, this is a special mess if we consider that PowerShell is higly object-oriented. To define your own classes to work with, you have to define them in another language (C# in this example):<\/p>\n<pre class=\"toolbar-overlay:false lang:ps decode:true\">Add-Type @'\r\n\r\nusing System;\r\n\r\npublic class DatabaseBackup\r\n{\r\n    public string instanceName;\r\n    public string databaseName;\r\n    public string recoveryMode;\r\n    public string status;\r\n    public DateTime creationTime;\r\n    public DateTime lastFull;\r\n    public DateTime lastLog;\r\n\r\n    private TimeSpan diff;\r\n\r\n    public double lastFullTotalHours () {\r\n\t\tdiff = DateTime.Now - lastFull;\r\n\t\treturn Math.Round(diff.TotalHours,2);\r\n    }\r\n\r\n    public double lastLogTotalHours () {\r\n\t\tdiff = DateTime.Now - lastLog;\r\n\t\treturn Math.Round(diff.TotalHours,2);\r\n    }\r\n\r\n}\r\n'@<\/pre>\n<p>For better code reading, I&#8217;ve put this definition in a separate file (DatabaseBackup.ps1).<\/p>\n<p><strong>The query that retrieves the data&#8230;<\/strong><\/p>\n<p>Actually I use this query to get the information:<\/p>\n<pre class=\"toolbar-overlay:false lang:default decode:true\">$query_bck_database = \"select \r\n  [Database]=d.name,LastFull,LastTran,GetDate=getdate(), \r\n  RecoveryMode=DATABASEPROPERTYEX(d.name, 'Recovery'),\r\n  CreationTime=d.crdate, Status=DATABASEPROPERTYEX(d.name, 'Status')\r\nfrom master.dbo.sysdatabases d\r\nleft outer join\r\n (select database_name, LastFull=max(backup_finish_date)\r\n        from msdb.dbo.backupset\r\n        where type = 'D' and backup_finish_date &lt;= getdate()\r\n        group by database_name\r\n ) b\r\non d.name = b.database_name\r\nleft outer join\r\n (select database_name, LastTran=max(backup_finish_date)\r\n        from msdb.dbo.backupset\r\n        where type ='L' and backup_finish_date &lt;= getdate()\r\n        group by database_name\r\n ) c\r\non d.name = c.database_name\r\n where d.name &lt;&gt; 'Tempdb'\r\norder by [LastFull]\";<\/pre>\n<p>I&#8217;ve also put this snippet in a separate file queries.ps1 to improve readability.<\/p>\n<p><strong>The tables<\/strong><\/p>\n<p>The <strong>first table<\/strong> (<em>DB_Servers<\/em>) can be as simple as a single column containing the instances to check. This can be any other kind of source like a corporate CMDB or similar.<\/p>\n<p>The <strong>second table<\/strong> will contain the data collected. Off course it can be expanded!<\/p>\n<p>The <strong>third table<\/strong> will contain some rules for managing exceptions. Such exceptions can be useful if you have situations like &#8220;all databases named northwind should not be checked&#8221;. I&#8217;ll show some examples in the next post.<\/p>\n<pre class=\"toolbar-overlay:false lang:default decode:true\">CREATE TABLE [dbo].[DB_Servers] (\r\n    [DB_Instance] [nvarchar](40) NOT NULL,\r\n    CONSTRAINT [PK_DB_Servers] PRIMARY KEY (DB_Instance)\r\n)\r\nGO\r\n\r\nCREATE TABLE [dbo].[DB_Status](\r\n\t[InstanceName] [varchar](50) NOT NULL,\r\n\t[DatabaseName] [varchar](50) NOT NULL,\r\n\t[RecoveryMode] [varchar](12) NULL,\r\n\t[DatabaseStatus] [varchar](15) NULL,\r\n\t[CreationTime] [datetime] NULL,\r\n\t[LastFull] [datetime] NULL,\r\n\t[LastLog] [datetime] NULL,\r\n\t[LastUpdate] [datetime] NULL,\r\n    PRIMARY KEY CLUSTERED ([InstanceName] ASC,[DatabaseName] ASC)\r\n)\r\nGO\r\n\r\nCREATE TABLE [dbo].[DB_Backup_Exceptions](\r\n\t[InstanceName] [varchar](50) NOT NULL,\r\n\t[DatabaseName] [varchar](50) NOT NULL,\r\n\t[LastFullHours] [int] NULL,\r\n\t[LastLogHours] [int] NULL,\r\n\t[Description] [varchar](250) NULL,\r\n\t[BestBefore] [datetime] NULL,\r\n    PRIMARY KEY CLUSTERED ([InstanceName] ASC,[DatabaseName] ASC)\r\n)\r\nGO<\/pre>\n<p>&nbsp;<\/p>\n<p><strong>The main code<\/strong><\/p>\n<p>Change this to whatever you want&#8230;<\/p>\n<pre class=\"toolbar-overlay:false lang:ps decode:true\"> set-location \"K:\\TOOLS\\CHECK_SQL\\\"\r\n\r\n### environment to insert results\/get lists\r\n$serverName = \"SERVER01\\MSSQL01\"\r\n$databaseName = \"Tools\"<\/pre>\n<p>This initializes the files explained earlier:<\/p>\n<pre class=\"toolbar-overlay:false lang:ps decode:true\">## initialise a file with some variables containing queries (to offload the script)\r\n. .\\queries.ps1\r\n\r\n## initialise a class to better manage database backups as objects\r\n. .\\DatabaseBackup.ps1<\/pre>\n<p>This adds the required snap-in to query sqlserver<\/p>\n<pre class=\"toolbar-overlay:false lang:ps decode:true\">if ( (Get-PSSnapin -Name sqlserverprovidersnapin100 -ErrorAction SilentlyContinue) -eq $null ) {\r\n    Add-PsSnapin sqlserverprovidersnapin100\r\n}\r\nif ( (Get-PSSnapin -Name sqlservercmdletsnapin100 -ErrorAction SilentlyContinue) -eq $null ) {\r\n    Add-PsSnapin sqlservercmdletsnapin100\r\n}<\/pre>\n<p>The following function will, given the instance, do the following:<\/p>\n<ul>\n<li>Get the data in a ResultSet<\/li>\n<li>Instantiate an instance of the DatabaseBackup class (the one we defined in the external file) for each row<\/li>\n<li>Return an array of DatabaseBackup objects with all the data ready to be processed<\/li>\n<\/ul>\n<pre class=\"toolbar-overlay:false lang:ps decode:true\">function getDatabaseBackups ([String]$instance) {\r\n\r\n  Write-Output \"    Instance: $instance\"\r\n  $databases = invoke-sqlcmd -Query  $query_bck_database -Server $instance\r\n\r\n  $i = 0\r\n  foreach ( $database in $databases ) {\r\n    $dbbck = new-object DatabaseBackup\r\n    $dbbck.instanceName = $instance\r\n    $dbbck.databaseName = $database.Database\r\n    $dbbck.recoveryMode = $database.RecoveryMode\r\n    $dbbck.creationTime = $database.CreationTime\r\n    $dbbck.status = $database.Status\r\n    if ( -not ( $database.IsNull(\"LastFull\") ) ) {\r\n      $dbbck.lastFull = $database.LastFull\r\n    } else {\r\n      $dbbck.lastFull = \"01.01.1900 00:00:00\"\r\n    }\r\n    if ( -not ( $database.IsNull(\"LastTran\") ) ) {\r\n      $dbbck.lastLog = $database.LastTran\r\n    } else {\r\n      $dbbck.lastLog = \"01.01.1900 00:00:00\"\r\n    }\r\n\r\n    [DatabaseBackup[]]$databasebackups += $dbbck\r\n  }\r\n  return $databasebackups\r\n}<\/pre>\n<p>This is the real &#8220;<strong>main<\/strong>&#8221; of the script, connecting to the central instance and getting the list of the instances to check:<\/p>\n<pre class=\"toolbar-overlay:false lang:ps decode:true\">$Connection = New-Object System.Data.SQLClient.SQLConnection\r\n$Connection.ConnectionString =\"Server=$serverName;Database=$databaseName;trusted_connection=true;\"\r\n$Connection.Open()\r\n\r\n$Command = New-Object System.Data.SQLClient.SQLCommand\r\n$Command.Connection = $Connection\r\n\r\n$instances = invoke-sqlcmd -Query \"select [name]=db_instance from db_servers\" -ServerInstance $serverName -Database $databasename<\/pre>\n<p>Finally, for each instance we have to check, we trigger the function that collects the data and we insert the results in the central repository (I&#8217;m using a merge to update the existent records).<\/p>\n<pre class=\"toolbar-overlay:false lang:ps decode:true\">foreach ( $instance in $instances ) {\r\n\r\n  $databasebackups = getDatabaseBackups ($instance.name);\r\n\r\n  $databasebackups[1..($databasebackups.length-1)] | foreach {\r\n\r\n    $_ | select-object instanceName,databaseName\r\n\r\n    $Command.CommandText = \"MERGE DB_Status as target USING (\r\n  select '$($_.instanceName )','$($_.databaseName )','$($_.recoveryMode )','$($_.status )','$($_.creationTime)','$($_.lastFull)','$($_.lastLog)')\r\n  as source (InstanceName, DatabaseName, RecoveryMode, DatabaseStatus, CreationTime, LastFull, LastLog)\r\nON (source.InstanceName=target.InstanceName and source.DatabaseName=target.DatabaseName)\r\n WHEN MATCHED THEN\r\n  UPDATE SET RecoveryMode = source.RecoveryMode, DatabaseStatus = source.DatabaseStatus, CreationTime = source.CreationTime,\r\n   LastFull = source.LastFull, LastLog = source.LastLog, LastUpdate=getdate()\r\n WHEN NOT MATCHED THEN\r\n  INSERT (InstanceName, DatabaseName, RecoveryMode, DatabaseStatus, CreationTime, LastFull, LastLog, LastUpdate)\r\n   VALUES (source.InstanceName, source.DatabaseName,source.RecoveryMode,source.DatabaseStatus, source.CreationTime, source.LastFull,source.LastLog,getdate() );\r\n\"\r\n    $Command.ExecuteNonQuery() | out-null\r\n  }\r\n  Remove-Variable databasebackups\r\n}\r\n$Connection.Close()<\/pre>\n<p><strong>How to use it<\/strong><\/p>\n<ul>\n<li>Create the tables and insert your instances in the table db_servers.<\/li>\n<li>Put the three files (<a href=\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2013\/05\/Collect_Backup_Data.ps1_1.txt\">Collect_Backup_Data.ps1<\/a>,<a href=\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2013\/05\/queries.ps1_.txt\">queries.ps1 <\/a>and <a href=\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2013\/05\/DatabaseBackup.ps1_.txt\">DatabaseBackup.ps1<\/a>) in a directory, modify the instance name and db name in Collect_Backup_Data.ps1<\/li>\n<li>Schedule the main script using the SQLAgent\u00a0 as a Operating system (CmdExec):<\/li>\n<\/ul>\n<pre class=\"toolbar-overlay:false lang:default decode:true\">powershell.exe -file \"K:\\TOOLS\\CHECK_SQL\\Collect_Backup_Data.ps1\"<\/pre>\n<ul>\n<li>You can&#8217;t use the internal powershell of SQLServer because it&#8217;s not full compatible with powershell 2.0.<\/li>\n<li>Check that the table db_status is getting populated<\/li>\n<\/ul>\n<p><strong>Limitations<\/strong><\/p>\n<ul>\n<li>The script use Windows authentication, assuming you are working with a centralized domain user. If you want to use the SQL authentication (example if you are a multi-tenant managed services provider) you need to store your passwords somewhere&#8230;<\/li>\n<li>This script is intended to be used with single instances. It should works on clusters but I haven&#8217;t tested it.<\/li>\n<li>Check the backup chain up to the tape library. <span style=\"text-decoration: underline;\">Relying on the information contained in the msdb is not a reliable monitoring solution!!<\/span><\/li>\n<\/ul>\n<p>In my next post we&#8217;ll see how to generate HTML reports via email and manage exceptions.<\/p>\n<p>Hope you&#8217;ll find it useful.<\/p>\n<p><span style=\"text-decoration: underline;\"><strong>Again PLEASE, if you improve it, kindly send me back a copy or blog it and post the link in the comments!<\/strong><\/span><\/p>\n<p>Next: <a href=\"https:\/\/www.ludovicocaldara.net\/dba\/sqlserver-centralized-backup-monitoring-with-powershell-and-tsql-22\/\">SQLServer centralized backup monitoring with PowerShell and TSQL (2\/2)<\/a><\/p>\n<p>Cheers<\/p>\n<p>&#8212;<\/p>\n<p>Ludo<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Checking database backups has always been one of the main concerns of DBAs. With Oracle is quite easy with a central RMAN catalog, but with other databases\u00a0doing it with few effort can be a great challenge. Some years ago I &hellip; <a href=\"https:\/\/www.ludovicocaldara.net\/dba\/sqlserver-centralized-backup-monitoring-with-powershell-and-tsql-12\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,132],"tags":[66,68,67,61,65,60,69],"class_list":["post-246","post","type-post","status-publish","format-standard","hentry","category-sqlserver","category-triblog","tag-backup","tag-check","tag-monitoring","tag-powershell","tag-script","tag-sqlserver-2","tag-tsql"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/246","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/comments?post=246"}],"version-history":[{"count":16,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/246\/revisions"}],"predecessor-version":[{"id":267,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/246\/revisions\/267"}],"wp:attachment":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/media?parent=246"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/categories?post=246"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/tags?post=246"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}