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.
set "_SI=.\V2014" set "_MPPlanName=MaintenancePlan-Lab" set "_SubPlan1=\Package\Subplan_1" DTEXEC.EXE /SQL "%_MPPlanName%" /Server %_SI%
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.
- And, yes Virginia we are connecting to our intended SQL Server Instance
We are invoking msdb.dbo.sp_ssis_getpackage passing along
- A nvarchar which is the name of the Maintenance Plan we sent along
- And, a uniqueIdentifier, which happens to be all zero
Let us go see what parameters msdb.dbo.sp_ssis_getpackage expects
exec msdb.dbo.sp_help '[dbo].[sp_ssis_getpackage]'
- It expects the package name, as @name
And, the folder ID as a Unique Identifier
I really nice to query Microsoft’s intensive metadata system views and see what data is exposed and their connected-ness.
Here we go!
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]
So there we have it…
- The Folder’s name is “Maintenance Plans“
- The Plan or Package name is “MaintenancePlan-Lab“
- The Sub Plan Name is “Subplan_1“
set "_SI=.\V2014" set "_MPFolder=Maintenance Plans" set "_MPPlanName=MaintenancePlan-Lab" set "_MPFullName=%_MPFolder%\%_MPPlanName%" DTEXEC.EXE /SQL "%_MPFullName%" /Server %_SI%
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
Unfortunately, ran successfully, but nothing happened.
Returning back to “Running a Maintenance Plan Using dtexec.exe Command Prompt Utility Does Not Perform Any Action“…
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;
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
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.
Let us set our Plan’s Disable Flag to “False”
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%
Post setting the Plan’s Disable Flag, Ran the revised command file and we are still good.
Dedicated to MSFT’s Funcion1. Here is his profile.
Listening to Kenny Chesney, “When I think about leaving”
And, it is here
And, here is a little bit of the lyrics….
It goes like
How we build this love we share on faith and trust, honest way we talk, tender way we touch