Thursday, September 17, 2009

Count Log-shipped databases

As part of my job in the Environment Hardening group, I needed to discover the usage of log-shipping in our environment. The script below generates a count of log-shipped databases.  It demonstrates how to use SQL Server Management Objects to find the Major version of SQL Server, the Switch statement, running a SQL query and summarizing the results using the measure-object cmdlet.

We wanted to be sure log-shipping was in use where the Recovery Point Objective and Recovery Time Objective required it.

1:  # .\CountLogShippedDBs.ps1
2:  [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
3:  # Count log shipped databases
4:  foreach ($svr in get-content "C:\Input\ProdInstances.txt" | where {$_ -notmatch "^#"})
5:  {
6:  $svr
7:  $s = New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"
8:  $ver = $s.Information.Version.Major
9:  $con = "server=$svr;database=master;Integrated Security=sspi"
10:   
11:  switch($ver)
12:  {
13:  {$_ -eq '8'}
14:  {$cmd = "IF OBJECT_ID('msdb.dbo.log_shipping_databases') IS NOT NULL SELECT @@SERVERNAME, COUNT(*) FROM msdb.dbo.log_shipping_databases"}
15:  {$_ -eq '9'}
16:  {$cmd = "IF OBJECT_ID('msdb.dbo.log_shipping_primary_databases') IS NOT NULL SELECT @@SERVERNAME, COUNT(*) FROM msdb.dbo.log_shipping_primary_databases"}
17:  {$_ -eq '10'}
18:  {$cmd = "IF OBJECT_ID('msdb.dbo.log_shipping_primary_databases') IS NOT NULL SELECT @@SERVERNAME, COUNT(*) FROM msdb.dbo.log_shipping_primary_databases"}
19:  }
20:  $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)
21:  $dt = new-object System.Data.DataTable
22:  trap {"Oops! $_"; continue } $da.fill($dt) | out-null
23:  $sum += $dt
24:  }
25:   
26:  $sum | measure-object -property Column2 -sum
27: