SQL Server – Running Maintenance Plans from the Command Line

Background

Here I am with a nice newly written SQL Server Maintenance Plan ( MP ), but I am unable to try it out.

If you politely asked why, I will say that “SQL Server Agent” is not running on my Development machine.

And, again if you nicely asked why, I will say that Corporate Policy insists that we regular folks SHALL NOT, note I say SHALL NOT, not SHOULD NOT, have Administrator access on our boxes.

Unfortunately, for yours truly during the moratorium period, I should have configured “SQL Server Agent” to auto-start.

But, now as it was not configured that way, I am stuck.

Run the Maintenance Plan from Console

Started cmd.exe and let us see how we can get dtexec binary ( dtexec.exe ) to properly execute our MP.

Attempt 1

Code


set "_SI=.\V2014"

set "_MPPlanName=MaintenancePlan-Lab"

set "_SubPlan1=\Package\Subplan_1"

DTEXEC.EXE /SQL "%_MPPlanName%" /Server %_SI%

Output

Screen Shot

Textual


DTEXEC.EXE /SQL "MaintenancePlan-Lab" /Server .\V2014

Microsoft (R) SQL Server Execute Package Utility

Version 12.0.5000.0 for 32-bit

Copyright (C) Microsoft Corporation. All rights reserved.

Started: 7:08:16 AM

Could not load package "MaintenancePlan-Lab" because of error 0xC001000A.

Description: The specified package could not be loaded from the SQL Server database.

Source:

Started: 7:08:16 AM

Finished: 7:08:17 AM

Elapsed: 0.219 seconds

SQL Server Profiler

Run SQL Server Profiler and see what is being passed to the Database Engine and also see if contact is even being made.

Screen Shot

Explanation

  1. And, yes Virginia we are connecting to our intended SQL Server Instance
  2. We are invoking msdb.dbo.sp_ssis_getpackage passing along
    1. A nvarchar which is the name of the Maintenance Plan we sent along
    2. And, a uniqueIdentifier, which happens to be all zero

msdb.dbo.sp_ssis_getpackage

Let us go see what parameters msdb.dbo.sp_ssis_getpackage expects

Code


exec msdb.dbo.sp_help '[dbo].[sp_ssis_getpackage]'

Output

Explanation

  1. It expects the package name, as @name
  2. And, the folder ID as a Unique Identifier

Metadata

I really nice to query Microsoft’s intensive metadata system views and see what data is exposed and their connected-ness.

Here we go!

Code


select 
		  [folderID] = tblSPF.folderid
		, [folder] = tblSPF.[foldername]
		, [package] = tblSP.[name]	
		, [planID] = tblSP.id
		, [subplanName] = tblSMPSP.[subplan_name]
		, [subplanDescription] = tblSMPSP.[subplan_description]
		, [sqlAgentJobID]  = tblSMPSP.[job_id]

from   [msdb].[dbo].[sysssispackages] tblSP

inner join [msdb].[dbo].[sysssispackagefolders] tblSPF

		on tblSP.folderid = tblSPF.[folderid]

inner join [msdb].[dbo].[sysmaintplan_subplans] tblSMPSP

		on tblSP.[id] = tblSMPSP.[plan_id]

Output

listplansandsubplans

Explanation

So there we have it…

  1. The Folder’s name is “Maintenance Plans
  2. The Plan or Package name is “MaintenancePlan-Lab
  3. The Sub Plan Name is “Subplan_1

 

Attempt 2

Code

set "_SI=.\V2014"

set "_MPFolder=Maintenance Plans"
set "_MPPlanName=MaintenancePlan-Lab"
set "_MPFullName=%_MPFolder%\%_MPPlanName%"

DTEXEC.EXE /SQL "%_MPFullName%" /Server %_SI% 

Output

Screen Shot

invoke_02

Textual


DTEXEC.EXE /SQL "Maintenance Plans\MaintenancePlan-Lab" /Server .\V2014  
Microsoft (R) SQL Server Execute Package Utility
Version 12.0.5000.0 for 32-bit
Copyright (C) Microsoft Corporation. All rights reserved.

Started:  8:27:13 AM
DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  8:27:13 AM
Finished: 8:27:13 AM
Elapsed:  0.218 seconds


Explanation

Unfortunately, ran successfully, but nothing happened.

 

Attempt 3

Returning back to “Running a Maintenance Plan Using dtexec.exe Command Prompt Utility Does Not Perform Any Action“…

Link

I checked that by running this sentence as the dtexec.exe parameter successfully run the backup job, in my case this is the command prompt instruction I used:

DTEXEC.EXE /SQL “Maintenance Plans\Backup MP” /Server SQL2005\YUKON /SET “\Package\Subplan_1.Disable“;false

The “tricky” part here refers to the /SET switch. As you can find in the maintenance plan GUI each maintenance plan has one or more subplans associated and this/theses are disabled by default so you need to enable them by issuing the “false” option for the “Disable” property of this subplan.

Remember that each subplan defined in the maintenance plan creates a job under SQL Server Agent.

In essence,  we need to fetch the Sub Plan name and use the Set argument to arrest its inbuilt disable flag; which by default is set to true;

Code


set "_SI=.\V2014"

set "_MPFolder=Maintenance Plans"
set "_MPPlanName=MaintenancePlan-Lab"
set "_MPFullName=%_MPFolder%\%_MPPlanName%"

rem set "_SubPlan1=\Package\Subplan_1"
set "_SubPlanName=Subplan_1"
set "_SubPlanFullname=\Package\%_SubPlanName%"

rem Added set statement to offset disable flag
DTEXEC.EXE /SQL "%_MPFullName%" /Server %_SI%  /SET "%_SubPlanFullName%.Disable";false

Output

Screen Shot

invoke_03

 

Re-Design the Plan

Let us use SQL Server Management Studio, connect to the SQL Server Instance, and access Management\Management Plans\ [Management Plan], and observe our Disable property for the Plan.

Plan

Original Plan

maintenanceplan-subplan-20161006-0842am-disabletrue-edited

 

Revised Plan

Let us set our Plan’s Disable Flag to “False

maintenanceplan-subplan-20161006-0847am-disablefalse

 

Attempt 4

Code


set "_SI=.\V2014"

set "_MPFolder=Maintenance Plans"
set "_MPPlanName=MaintenancePlan-Lab"
set "_MPFullName=%_MPFolder%\%_MPPlanName%"

rem Removed set statement to offset disable flag
DTEXEC.EXE /SQL "%_MPFullName%" /Server %_SI%  

Output

Post setting the Plan’s Disable Flag, Ran the revised command file and we are still good.

 

Dedicated

Dedicated to MSFT’s Funcion1. Here is his profile.

Listening

Listening to Kenny Chesney, “When I think about leaving

And, it is here

And, here is a little bit of the lyrics….

whenithinkaboutleaving

It goes like

How we build this love we share on faith and trust, honest way we talk, tender way we touch

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s