Mistake: checking backup jobs one instance at a time. A Central Management Server organizes registered SQL Server instances into groups, allowing one query to run across the estate and identify the instance behind each result. section 12.4.1
Configure it through Registered Servers:
- Create a Central Management Server in SQL Server Management Studio. section 12.4.1
- Create a Server Group beneath it and register every instance. section 12.4.1
- Run a consistent backup-job query each morning; the output adds a Server column so failed jobs can be traced to an instance. section 12.4.1
Failed backup check
Use a query that ranks failed executions with ROW_NUMBER(), partitions by Server, and keeps the latest execution for the consistently named backup job. Replace 'Backups' with the actual job name. section 12.4.1
Listing 12.2 Retrieving failed backup jobs for all servers
SELECT
Server
, name
, message
, run_status
FROM (
SELECT
Server
, j.name
, jh.message
, jh.run_status
, ROW_NUMBER() OVER(PARTITION BY Server ORDER BY run_date, run_time DESC) AS RowNumber
FROM msdb.dbo.sysjobhistory jh
INNER JOIN msdb.dbo.sysjobs j
ON j.job_id = jh.job_id
WHERE jh.run_status = 0
AND j.name = 'Backups'
) Results
WHERE RowNumber = 1 ;
Inspect the T-SQL pattern for retrieving failed backup jobs across all registered servers. section 12.4.1
