SQL Server SMO is a great resource for DBAs, unfortunately, before PowerShell, it is a pretty deep learning curve to get familiar with SMO. The beautify of PS is its own discovery capacity. This is best described in the Keith Hill’s free ebook (http://rkeithhill.wordpress.com/2009/03/08/effective-windows-powershell-the-free-ebook/). So in short, we can use get-member cmdlet to find out various properties / methods of an SMO object.
Case Scenario: (Find databases without the recent differential backup)
We have thousands of databases on a few hundred instances, and we have the regular weekly full backup, daily diff backup and 30 min tlog backup schedules for all databases with full recovery model.
Every morning, we want to make sure we have the successful diff backups of each database. If there is a database that does not have a differential backup file for whatever reason, we want to find it out.
Assumption: all differential backups start after 12:00am (can be at 12:00am or 1:00am or whenever) but will finish before 8:00am.
PowerShell Solution:
Run the following PS script at 8:00am everyday (assuming all sql instances are in the same time-zone and your domain has the correct privileges to log into each server)
# tested on windows 7/ sql server 2012 / PS V3, against sql server 2005+ instances
add-type -AssemblyName "Microsoft.SqlServer.SqlWmiManagement, version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";
#note: tp520 is my local comuter name in this script
'tp_w520\sql2k8r2', 'tp_w520' | % { $s=new-object "Microsoft.sqlserver.management.smo.server" $_; $s.databases |
? {($_.recoverymodel).tostring() -eq 'full' -and ($_.LastDifferentialBackupDate -le (get-date).AddHours(-8)) -and ($_.name -notin ('model', 'msdb', 'master'))} } |
select @{l='Server'; ex={ ($_.Parent).name}}, Name | ft -AutoSize
The sample result is like the following:
Advantage Analysis:
Originally, my t-sql solution is that I have created all the linked servers on my dedicated monitoring server, and I will loop through all the linked servers, using a dynamic sql to check [linked-server].msdb.dbo.backupset to see which databases have a successful differential backup and I also need to join with master.sys.databases to finally get those databases in question. It works but it is really not neat and tidy.
With PS, we even do not need to create any linked servers and of course, no dynamic t-sql needed.
Other Thoughts:
This approach also applies to check full backup / tlog backup. For example, if tlog backup is scheduled to run every 30 min, and usually finish within 30 min, it makes sense to say that if there is a database which has not a successful tlog backup for the past 2 hrs, this database needs to be signalled out. All you need to do is that in the script, change “$_.LastDifferentialBackupDate” to “$_.LastLogBackupDate” and change AddHours(-8) to AddHours(-2)
I also use SMO database object to collect each database’s size, data space usage, index space usage info and dump them into a table. This is very useful to monitor each db’s growth status and very handy when doing storage capacity planning.
