Is it possible to retrieve the specific databases selected in a maintenance plan step?

Question

I would like to use a tsql 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?

Answer 1

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"

Answer 2

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).