Solved

Run Maintenance Plan From Query

Posted on 2006-11-25
7
338 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL date incremented 11 31
Inserting oldest record into new table. 5 25
Insert query into temp tables using Coldfusion 3 22
Merge two rows in SQL 4 17
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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 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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

832 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