Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

up vote 1 down vote accepted

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"
share|improve this answer
    
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 '12 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 '12 at 17:55
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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