[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Run Maintenance Plan From Query

Posted on 2006-11-25
7
Medium Priority
?
382 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:Phil Chapman
  • 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:Phil Chapman
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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:Phil Chapman
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 1000 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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

607 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