Cupfighter.net

Ever thought you had a good backup and it turned out it was missing when you needed a restore? Although a regular restore test is the least you can do, there is more. SQL Server stores backupinformation for all databases in msdb.dbo.backupset. A simple query shows you all databases that have no backup in the last two days:
SELECT DatabaseName = d.name
,LatetstBackUpDate = ISNULL(CONVERT(VARCHAR(30),MAX(b.backup_finish_date),120),’No backup’)
,’Days’ = MIN(DATEDIFF(hh,b.backup_finish_date,getdate())) / 24
,’Hours’ = MIN(DATEDIFF(hh,b.backup_finish_date,getdate())) % 24
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name
WHERE d.name NOT IN (‘tempdb’,'model’)
GROUP BY d.name
HAVING ISNULL(MIN(DATEDIFF(hh,b.backup_finish_date,getdate())),999) >= 24 * 2 — change the 2 when you want to use a different amount of days
ORDER BY d.name

Wait, there is more! You don’t want to run this query every day by hand is it? Of course you can run it as a scheduled job on your database server, but even that can be a time-consuming task if you have a lot of servers. It would be better to run this query from System Center Operations Manager.

You can add the query in a System Center Opeations Management Monitor to deploy your backup check automatically to *all* SQL Servers within your organization. I’ll show you how to do it:
Create a “Timed Script Two State Monitor” (don’t forget to select an appropriate management pack to save your monitor).
Set the Monitor target to “SQL 2005 DB engine:

In the next step, choose your schedule (once a day would be appropriate in most cases).
In the script page, name your script (don’t forget to add .vbs extension).
Paste the following script in the script box:


Dim i, oAPI, propertyBag, objShell
Dim strStatus
Dim objParameters, sConnectionString

' Return values to SCOM to set state and generate Alert
Set propertyBag = oAPI.CreatePropertyBag ()
Call propertyBag.AddValue ("Status", strStatus)
Call propertyBag.AddValue ("DBList", strList)
Call oAPI.Return(propertyBag)


Click on the Button “Paramaters…” at the bottom of this step.
Use the following code here, or select “Connection String (SQL DB Engine)” from the Target button.

$Target/Property[Type="MicrosoftSQLServerLibrary6064600!Microsoft.SQLServer.DBEngine"]/ConnectionString$

In the Unhealthy Expression step, add the following:Property[@Name='Status] Equals Warning

For the healthy expression add: Property[@Name='Status'] Equals Healthy
In the next steps configure Health and Alerts according to your needs.

In the alert description you can use a text like this:
No SQL backup in last 48 hours on $Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetbiosComputerName$ in instance $Target/Property[Type="MicrosoftSQLServerLibrary6064600!Microsoft.SQLServer.ServerRole"]/InstanceName$, list of databases: $Data/Context/Property[@Name='DBList']$

You are all done. The monitor will automatically run on all SQL Server instances to check when the last backup is made and warn you if that’s more than 2 days ago.