August 19, 2011 at 12:13 pm
Hello!
I’m trying to develop a script to list SQL Agent jobs and the databases they back up on the servers as well as the location of the backup files. So far, I’m able to list jobs, job steps. On a separate list, I was able to list database names, their logical, physical names as well where they are backed up to.
I’m not able to join the two lists together as the key to the sysjobs/sysjobsteps/sysjobschedules is job_id and they key to the backupfile/backupset/backupmediafamily is the backup_set_id/media_set_id.
Do you know of a common key to join these two set of listings together?
Thank you for your help.
Loi.
Here's the scripts I have for both list:
List1: List SQL Agent jobs
select
j.name
,j.enabled
,js.step_name
,js.subsystem
--,js.server
--,js.database_name
from dbo.sysjobs j
left join dbo.sysjobsteps js
on j.job_id = js.job_id
order by name
List2: List the database names and where they are backed up to:
select
bs.server_name
,bs.backup_set_id
,bs.media_set_id
,bs.database_name
,bf.logical_name
,bf.physical_drive
,bf.physical_name
,mf.physical_device_name backup_location
,bs.expiration_date
from dbo.backupfile bf
join dbo.backupset bs
on bf.backup_set_id = bs.backup_set_id -- > DBNAME , key=backup_set_id, ==> media_set_id
Join dbo.backupmediafamily mf
on bs.media_set_id = mf.media_set_id
order by Database_name
August 19, 2011 at 12:22 pm
I doubt if there is a relationship between backupset table and jobs table. You can take a backup without a job and this would still make an entry in backupset table.
August 19, 2011 at 12:30 pm
Is there a way to tell where the SQL Agents backup jobs back up the data to without manually looking into the job themselves?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply