August 1, 2014 at 7:01 am
As part of my backup routine, I have a SQL job for each DB which which is called "DB-NAME - Backup Job"
I need to put a script together to check that each database has a backup job associated to it.
I have no idea how to go around this
select * From sys.sysdatabases where name not in ('msdb','model', 'master', 'Tempdb', 'DBA') order by name desc
select * from msdb.dbo.sysjobs order by name desc
I know all the details i need are in there, but i cant figure out the best way to tackle it. Do I need to do a cursor to go through each DB name, put it into a variable, then select the job name where name like '$variable%' ?? Any ideas?
August 1, 2014 at 7:18 am
SQLSteve (8/1/2014)
As part of my backup routine, I have a SQL job for each DB which which is called "DB-NAME - Backup Job"I need to put a script together to check that each database has a backup job associated to it.
I have no idea how to go around this
select * From sys.sysdatabases where name not in ('msdb','model', 'master', 'Tempdb', 'DBA') order by name desc
select * from msdb.dbo.sysjobs order by name desc
I know all the details i need are in there, but i cant figure out the best way to tackle it. Do I need to do a cursor to go through each DB name, put it into a variable, then select the job name where name like '$variable%' ?? Any ideas?
Can't completely help on this right now but I would recommend not using sys.sysdatabases. You should use sys.databases instead as sys.sysdatabases has been deprecated by Microsoft and may be removed from future versions of SQL Server. It is for backward compatibility to SQL Server 2000.
Question, from reading your post it looks like the database name is included in the job name?
August 1, 2014 at 7:30 am
Wasn't aware of that, thanks.
Yes thats right so its easy to make a link, we dont need to look into the step of the job or anything. Each job begins with the database name.
August 1, 2014 at 7:36 am
Give this a shot, I couldn't test it as I don't have the same the environment as you:
select
db.name as DatabaseName,
sj.name as JobName
from
sys.databases db
left outer join msdb.dbo.sysjobs sj
on (sj.name like db.name + '%')
order by
db.name;
August 1, 2014 at 7:44 am
I came up with a similar approach of hitting against the job name.
select d.name
from sys.databases d
except
select REPLACE(name, ' - Backup Job', '')
from msdb.dbo.sysjobs
order by name;
August 1, 2014 at 8:13 am
You certainly don't need a cursor, as others have shown you. However, may I suggest you approach this from a different angle? It's all very well checking you have a backup job with the database name in it for each database, but what if one of them is disabled, or not scheduled, or wrongly scheduled, or is backing up the wrong database? I think you'd be better checking the backupset table in msdb to verify that backups have actually taken place. You can use a script like the one below and schedule it to run regularly. I've got a central SSIS package that runs against all servers and compares the results with what is expected, checks that the backup files exist and reports any discrepancies.
SELECT
r.DBName
,r.[type]
,r.BackupStart
,s.backup_finish_date
,s.backup_size
,m.physical_device_name
FROM ( --gets most recent of each type of backup for each DB
SELECT
d.name AS DBName
,b.[type]
,MAX(b.backup_start_date) AS BackupStart
FROM
master..sysdatabases d
LEFT JOIN
msdb..backupset b
ON
d.name = b.database_name
JOIN
msdb..backupmediafamily f ON b.media_set_id = f.media_set_id
GROUP BY
d.name
,b.type
) r
JOIN
msdb..backupset s
ON
r.DBName = s.database_name AND r.[type] = s.[type] AND r.BackupStart = s.backup_start_date
JOIN
msdb..backupmediafamily m ON s.media_set_id = m.media_set_id
ORDER BY
r.DBName
,r.[type]
John
August 1, 2014 at 9:30 am
John Mitchell-245523 (8/1/2014)
You certainly don't need a cursor, as others have shown you. However, may I suggest you approach this from a different angle? It's all very well checking you have a backup job with the database name in it for each database, but what if one of them is disabled, or not scheduled, or wrongly scheduled, or is backing up the wrong database? I think you'd be better checking the backupset table in msdb to verify that backups have actually taken place. You can use a script like the one below and schedule it to run regularly. I've got a central SSIS package that runs against all servers and compares the results with what is expected, checks that the backup files exist and reports any discrepancies.
SELECTr.DBName
,r.[type]
,r.BackupStart
,s.backup_finish_date
,s.backup_size
,m.physical_device_name
FROM ( --gets most recent of each type of backup for each DB
SELECT
d.name AS DBName
,b.[type]
,MAX(b.backup_start_date) AS BackupStart
FROM
master..sysdatabases d
LEFT JOIN
msdb..backupset b
ON
d.name = b.database_name
JOIN
msdb..backupmediafamily f ON b.media_set_id = f.media_set_id
GROUP BY
d.name
,b.type
) r
JOIN
msdb..backupset s
ON
r.DBName = s.database_name AND r.[type] = s.[type] AND r.BackupStart = s.backup_start_date
JOIN
msdb..backupmediafamily m ON s.media_set_id = m.media_set_id
ORDER BY
r.DBName
,r.[type]
John
Same suggestion I made to the OP, really shouldn't use sysdatabases. Also, although the dbname..tablename defaults the the schema to dbo it really should be specified:
SELECT
r.DBName
,r.[type]
,r.BackupStart
,s.backup_finish_date
,s.backup_size
,m.physical_device_name
FROM ( --gets most recent of each type of backup for each DB
SELECT
d.name AS DBName
,b.[type]
,MAX(b.backup_start_date) AS BackupStart
FROM
master.sys.databases d -- or you could just say sys.databases
LEFT JOIN
msdb.dbo.backupset b
ON
d.name = b.database_name
JOIN
msdb.dbo.backupmediafamily f ON b.media_set_id = f.media_set_id
GROUP BY
d.name
,b.type
) r
JOIN
msdb.dbo.backupset s
ON
r.DBName = s.database_name AND r.[type] = s.[type] AND r.BackupStart = s.backup_start_date
JOIN
msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
ORDER BY
r.DBName
,r.[type]
August 1, 2014 at 9:33 am
Oof! Good spot, Lynn. I wrote that script years ago when it still had to work against SQL Server 2000. I'll update my package at the next opportunity.
John
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply