Solved

Run Maintenance Plan From Query

Posted on 2006-11-25
7
358 Views
Last Modified: 2012-05-05
I have a mainenance plan
DB Maintnance Plan 2
Is is possible to to execute a query that will run this maintenance plan

if so what is the syntax
0
Comment
Question by:PhilChapmanJr
  • 5
  • 2
7 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 18011591
Sure - you can run it with
exec EXEC xp_sqlmaint '-PlanID xxxxxxxx' WHERE xxxx is the plan ID.
To identify the plan ID, run:
exec sp_help_maintenance_plan.
0
 
LVL 29

Expert Comment

by:Nightman
ID: 18011600
Some references as to the switches that can be passed in, and other usage:

http://msdn2.microsoft.com/en-us/library/ms188408.aspx
http://www.transactsql.com/html/xp_sqlmaint.html
0
 
LVL 2

Author Comment

by:PhilChapmanJr
ID: 18011697
Nightman,

if I use
exec EXEC xp_sqlmaint '-PlanID 4F9F327D-27DD-4505-B016-00E5FF63BC4F'
I get a Incorrect syntax near the keywork 'EXEC'.

If i use
EXEC xp_sqlmaint '-PlanID 4F9F327D-27DD-4505-B016-00E5FF63BC4F'
I get a Could not find stored procedure 'xp_sqlmaint'.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Expert Comment

by:Nightman
ID: 18011707
Sorry about the double 'exec' - typo.

The extended stored proc is on the master database, so you would either have to connect to the master database first or run it like this:

EXEC master.dbo.xp_sqlmaint '-PlanID 4F9F327D-27DD-4505-B016-00E5FF63BC4F'
0
 
LVL 2

Author Comment

by:PhilChapmanJr
ID: 18011742
A report is displayed however in the Maintenance Plan there is a backup scheduled ( Not for this date and time ) is there a way to force the backup to run.
0
 
LVL 29

Accepted Solution

by:
Nightman earned 250 total points
ID: 18011753
You must use the switches (documented in the links I posted) to force the specific actions to take place. For example, if you wanted to execute the backup, you would do

EXEC master.dbo.xp_sqlmaint '-PlanID 4F9F327D-27DD-4505-B016-00E5FF63BC4F -VrfyBackup -BkUpMedia DISK -BkUpDB -UseDefDir -BkExt "BAK"'

Also, if you are going to do this from the SQLDMO, theoretically (I haven't done it myself) the report should be available in the QueryResults object.
0
 
LVL 29

Expert Comment

by:Nightman
ID: 18011759
There is another example of using this approach and storing results in the systables as well
http://www.sqlservercentral.com/columnists/awarren/sqlmaintenanceplans.asp
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
unable to reinstall SCVMM 2012 R2 - SQL errors. 5 34
push and Pull replication 31 45
SQL Query help 3 24
removing unwanted rows from an sql server ranked table 13 34
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question