Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Run Maintenance Plan From Query

Posted on 2006-11-25
7
Medium Priority
?
370 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

722 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