I wanted to share a powershell script I whipped up that adds the results directly to a SQL table:

Basically, you'd setup your CMS, add all your servers, and then run a SQL job that would execute the Powershell script which would add the records right back into your repository's backup log table for central reporting purposes. Obviously though you could tweak this to only pull yesterday's backups, or only insert newer backups, etc..

In your repository DB, make a table and procedure:

CREATE TABLE [dbo].[BackupLog](

[BackupLogID] [int] IDENTITY(1,1) NOT NULL,

[ServerName] [varchar](50) NOT NULL,

[DBName] [varchar](50) NOT NULL,

[BackupTypeID] [varchar](5) NOT NULL,

[LastBackup] [datetime] NULL,

[DayInterval] [float] NULL,

[CreateDate] [datetime] NOT NULL

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[BackupLog] ADD CONSTRAINT [DF_BackupLog_CreateDate] DEFAULT (GETDATE()) FOR [CreateDate]

GO

CREATE PROCEDURE [dbo].[spBackupAdd]

(@ServerName varchar(50), @DBName varchar(50),

@BackupTypeID varchar(5), @LastBackup datetime, @DayInterval float)

AS

INSERT INTO BackupLog

(ServerName, DBName, BackupTypeID, LastBackup, DayInterval)

VALUES (@ServerName, @DBName, @BackupTypeID, @LastBackup, @DayInterval)

GO

Then make a powershell script, e.g. BackupLogger.ps1, and fill in your respective Server/Database:

#Server/DB for your repository

$RepServer = "<ServerName>"

$RepDatabase = "<DBName>"

## SQL connection function

# removes the need to install the SQL extensions for PowerShell

function Invoke-Sqlcmd2

{

param([string]$ServerInstance,

[string]$Database,

[string]$Query,

[Int32]$QueryTimeout=30

)

$conn=new-object System.Data.SqlClient.SQLConnection

$conn.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $ServerInstance,$Database

$conn.Open()

$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)

$cmd.CommandTimeout=$QueryTimeout

$ds=New-Object system.Data.DataSet

$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)

[void]$da.fill($ds)

$conn.Close()

$ds.Tables[0]

}

## Begin Code

$con = "Server=$RepServer;Database=$RepDatabase;Integrated Security=True"

$cmd = "SELECT CASE WHEN PATINDEX('%,%', SRV.[server_name]) > 0 THEN

LEFT(SRV.[server_name], PATINDEX('%,%', SRV.[server_name])-1)

ELSE SRV.[server_name] END AS ServerName

FROM [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] SRV

JOIN [msdb].[dbo].[sysmanagement_shared_server_groups_internal] GRP

ON SRV.server_group_id = GRP.server_group_id"

$da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)

$dt = new-object System.Data.DataTable

$da.fill($dt) | out-null

foreach ($srv in $dt)

{

$ServerName = $srv.ServerName

$Reply = Test-Connection -ComputerName $ServerName -Count 1 -Quiet

# if ping returns then continue

if ($Reply –eq “True”)

{

$con2 = "Server=$ServerName;Database=msdb;Integrated Security=True"

$cmd2 = "SELECT db.name, b.type, MAX(b.backup_finish_date) LastSuccessfulBackup,

CAST((GETDATE() - MAX(b.backup_finish_date)) AS NUMERIC(5, 2)) IntervalInDays

FROM master..sysdatabases db

LEFT OUTER JOIN msdb..backupset b ON db.name = b.database_name

where b.type='D'

GROUP BY db.name, b.type

ORDER BY db.name, b.type"

$da2 = new-object System.Data.SqlClient.SqlDataAdapter ($cmd2, $con2)

$dt2 = new-object System.Data.DataTable

$da2.fill($dt2) | out-null

foreach ($backup in $dt2)

{

$BackupDB = $backup.name

$BackupDBType = $backup.type

$BackupDBLast = $backup.LastSuccessfulBackup

$BackupDBInt = $backup.IntervalInDays

$sql = "Exec spBackupAdd '$ServerName', '$BackupDB', '$BackupDBType', '$BackupDBLast', $BackupDBInt"

#Write-Host $sql

Invoke-Sqlcmd2 -serverinstance $RepServer -database $RepDatabase -query $sql

}

}

$Reply = ""

}

Then just create a SQL Agent job to run the script.

The case statement to strip out a comma I used when testing since my only server was the CMS server, so I had to register it as <ServerName>,port, e.g. MyServer,1433.

Bob