2

I would like to use a query to retrieve the databases involved in one of the steps in a database backup plan I've created. From a previous question, I understand that somehow, the data is probably stored in the msdb database, but running a trace didn't reveal the query that retrieved the "checked" status of each selected database.

Does anyone know how SQL Server determines which of the server's databases are included in a particular job step?

Improve this question

3 Answers 3

Reset to default
2

I don't believe there is a way. If you create a Maintenance Plan, it is going to be an SSIS package. Querying the msdb.dbo.sysjobsteps table, you can view the actual command, but for an SSIS package execution it won't be indicative to the particulare databases you are backing up:

select
    step_name,
    subsystem,
    command
from msdb..sysjobsteps

This will give you something similar to the following command:

/Server "$(ESCAPE_NONE(SRVR))" /SQL "Maintenance Plans\MaintenancePlan" /set "\Package\BackupDBs.Disable;false"
Improve this answer
2
  • Ah, OK, thanks. I'm not very familiar with SSIS, but I guess the next obvious question would be - any chance of using command line with SSIS to get the names (or is this more suitable for another question)?
    – Peter Majeed
    May 10, 2012 at 17:43
  • 1
    @PeterMajeed Unfortunately, I don't think that's possible either. See this reference (msdn.microsoft.com/en-us/library/ms162810%28v=sql.105%29.aspx) on the dtexec utility (used for SSIS execution from the command line).
    – Thomas Stringer
    May 10, 2012 at 17:55
2

This will show the log history of any maintenance plan that includes the current database. It shows each step and if it succeeded. line3 contains string of DBs used for the given executions.

SELECT
    ld.server_name,
    mp.name AS [MTX Plan Name],
    msp.subplan_name AS [Sub Plan Name],    
    mpl.start_time AS [JobStart],
    mpl.end_time AS [JobEnd],
    mpl.succeeded AS [JobSucceeded],
    ld.start_time AS [StepStart],
    ld.end_time AS [StepEnd],
    ld.succeeded  AS [StepSucceeded],
    ld.line1,
    ld.line2,
    ld.line3,
    ld.line4,
    ld.line5,
    ld.command
FROM
    msdb.dbo.sysmaintplan_plans mp
    INNER JOIN msdb.dbo.sysmaintplan_subplans msp ON mp.id = msp.plan_id
    INNER JOIN msdb.dbo.sysmaintplan_log mpl ON msp.subplan_id = mpl.subplan_id
    INNER JOIN msdb.dbo.sysmaintplan_logdetail ld ON mpl.task_detail_id = ld.task_detail_id
        AND ld.command LIKE ('%['+db_name()+']%')
ORDER BY
    mpl.start_time DESC
Improve this answer
1
  • will show the log history of any maintenance plan that includes the current database. It shows each step and if it succeeded. line3 contains string of DBs used for the given executions
    – Len Nagy
    Dec 14, 2018 at 0:13
1

Practically speaking, no, this is not possible.

Maintenance plans are stored as binary SSIS package images in msdb.dbo.sysssispackages and the image includes all the plan settings. In order to get the list of databases back, you would have to deserialize the raw package image somehow (undoubtedly fraught with peril... not recommended).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.