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
MCSA SQL 2014