.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Exporting SQL Maintenance Plans

Posted By:      Posted Date: September 07, 2010    Points: 0   Category :Sql Server
I have taken a look at the new maintenance plan designer and have come up with a maintenance plan I would like to be able to deploy on an enterprise level.  Anyone know of a way I can export the plan to another SQL 2005 server - without directly pulling from the msdb system tables?  I like the fact that you can script the output of most of the wizards, but the maintenance plan wizard does not appear to be one of them.Thanks,Matt

View Complete Post

More Related Resource Links

Cannot save maintenance plans

I am running SQL 2005 standard SP2 and I cannot save maintenance plans. Using the wizard I create the plan but it always fails with the following error messages:   An exception occurred while executing a Transact_SQL statement or batch   Invalid column name 'from_msx'. Invalid column name 'has_targets'. (Microsoft SQL Server , Error: 207)   Invalid column name 'msx_job_id'. (Microsoft SQL Server, Error: 207)   Thanks   Todd

Unable to modify Maintenance Plans SQL 2008

Been having a problem for a while, in that we are unable to modify any maintenance plans in SQL Server Management Studio. The server is running SLQ Server 2008 Standard on Windows 2003 Standard SP2

Can't save Maintenance Plans (10.50.1600 / 2008 R2 x64)

I run SQL Server 10.50.1600 / 2008 R2 x64 Enterprise. I am unable to create a maintenance plan via Management Studio. If I just try to create a new maintenance plan, then I get the following problem: "An OLE DB error 0x80004005 (Login timeout expired) occurred while enumerating packages. A SQL statement was issued and failed."

Error with Maintenance Plans - SQL 2005

Hi everyone.I have a problem with SQL 2005 with Cluster.  There is no issues with installation process and the cluster works fine. Normally I install SQL and apply Service Pack. So I do this, installing Service Pack 3 in both nodes (two nodes)After this, I create some databaese and restore backup from other SQL 2005 Server and also I import the logins to access SQLBut when I try access Maintenance Palns appears the following messageFailed to retrieve data for this request (Microsoft.SqlServer.SmoEnum)Additional Information:    an exception occurred while executing a Transact-SQL statement or batch (Microsoft.SqlServer.ConnectionInfo) Invalid column name 'from_msx'Invalid column name 'has_targets') Microsoft Sql Server, Error:207)What I do. Remove client components, Integration and Notification Services in both nodes, reinstall and apply Service Pack. Now I can create a new plan. but in the saving step appears the following messageSaving maitenance plan failed.Additional information:Failed to retrieve data for this request (Microsoft.SqlServer.SmoEnum)An exception occurred while executing a Transact-SQL statement or batch (Microsoft.SqlServer.ConnectionInfo) Invalid colum name 'ms_job_id' (Microsoft Sql Server, Error:207)I found some information in Internet, about a script that fixes this, but in Service Pack 2. I thinking in remove SQL , reinstall and test without SP. And

how can i export/import maintenance plans from 2005 to 2008?

I have this link http://sqlservernewbie.blogspot.com/2008/10/how-to-importexport-sql-server.html However, it is not the solution for me. When I expand SERVERNAME (Integration Services), all I can see it is Running packages and Stored packages, and I cannot expand any deeper. And the scripting way which described in the link above, seems is not finished. It just tells you about the select...JulieShop

Do cleanup maintenance plans that are linked to a backup job work for other backup jobs as well?



I have a job that backs up transaction logs and then there is another job within that plan that cleans up .trn files that are older than 48 hours. This job points to the directory where the transaction logs are backed up to. I just created a seperate maintenance plan to backup transaction logs for a different database. Will the cleanup job for the first backup job also clean up the logs from the second plan or do I need to create another job to cleanup the logs from the second backup job. My confusion is that the first backup job has specific databases that it is backing up and I'm wondering if that limits what the cleanup job does as well.

SQL SERVER 2008 Create Maintenance Plans encounter General Access Denied error


After I clicked New Maintenance Plans and filled in <Maintenance Plans Name>, an error appeared like below: General access denied error, my account is already sysadmin and public, and agent service has started.


TITLE: Microsoft SQL Server Management Studio


General access denied error


General access denied error



SQL Server 2005 Maintenance Plans - Reporting And Logging Does Not Work


I have a maintenance plan built on a SQL Server 2005 instance that rebuilds indexes, rebuilds statistics, checks for database integrity, performs a maintenance cleanup, and performs a history cleanup.  This job runs once per week.

In the "Reporting and Logging" section I have the following options:

- Generate a text file report is checked

- Create a new file is selected

- A valid destination folder is identified

- Send report to an email recipient is checked

- A valid agent operator is selected

- Log extended information is checked

The job seems to complete successfully and a report is created and emailed, but the only information in the report is as follows:

Quest SLSSQLMaint Utility.
Logging on to SQL Server 'DEVSQL01' with Windows Authentication
Logged on to SQL Server 'DEVSQL01'

[1] Delete old text report files...
[1] Execution Time: 0 hrs, 0 mins, 0 secs.

[2] Send DBMail or SendMail report file....

[2] Execution Time: 0 hrs, 0 mins, 1 secs.

End of maintenance plan 'DB Maintenance Plan (No Backups) [DB Maintenance]' on 9/29/2010 8:40:49 PM (Success)
C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Binn\slssqlmaint.exe Process Exit Code: 0 (Success)

Where is the information about the rest of the maintenance plan?&nbs

cannot see the maintenance plans menu on the management menu



i've read a few related themes, but i cannot find the one, i hope you can answer my question.

I just cannot see the title to create a maintenance plan. i've read you create one following this steps:

3. In SQL Server Management Studio, expand the Management node, rightclick Maintenance Plans, and then select Maintenance Plan Wizard.
4. On the Maintenance Plan Wizard welcome page, click Next.
5. On the Select Plan Properties page, type a name for the maintenance plan, click Next.

the problem is that i don't have de Maintenance Plans title. My menu node expands like this:

    ->SQL Server Logs

i need to create a new maintenance plan, but only appear the old m. plan.

thks. Diana


Maintenance Plan with Multiple Sub Plans with Multiple Schedules


I have a Maintenance Plan that have various other subplans. Each subplan has its own schedule. For instance, one will be an hourly log backup, a daily complete backup and a weekly maintenance subplan. Now when ever the log backup takes place, say subplan1 succeeds HOWEVER, the other Subplans subplan 2 and subplan 3 show errors on the error logs. I know that these messages dont matter, however the GLARING X's marks on the Maintenance Plan History does not do service. This way i will have to go in and check each history. Definitely not as simple as looking at Tick marks on the work well done.

IS there a solution. (other than creating a separate maintenance plan for each subplan)!!!

PLease HELP. Thanks. 

Maintenance Plans




We've have recently moved a SQL Server database from a local server to a dedicated server. On our local server we had maintenance plans setup to backup the database and they ran fine. The local server had SQL Server 2008 Standard Edition. However on the dedicated server, although we can successfully set maintenance plans, they are not executing. We are getting the following error message:

"The SQL Server Execute Package Utility requires Integration Services to be installed by one of these editions of SQL Server 2008: Standard, Enterprise, Developer, or Evaluation. To install Integration Services, run SQL Server Setup and select Integration Services.  The package execution failed.  The step failed."

The dedicated server has SQL Server 2008 Web Edition and we do not current have a spare license key for a Standard Edtion.

I have read some post on these forums that say if you install Service Pack 2 then maintenance plans will run without using Integration Services, however, we have installed Service Pack 2 and it still does not run.

So a few questions:

- other posts suggest installing the Integration Services component from the Standard Edition we have locally, but does this then become a breach of the licensing for the Web Edition?

- is it possible to purchase just the Integration Services

Best Practices for SQL Backups - Snapshots vs Replay Manager vs SQL Maintenance Plans vs Backup Exec

Hi, can anyone give me some information on the best practice for backing up sql backups?  For example, is it better to use snapshots vs SQL Maintenance plans, etc.  Any info is greatly appreciated.

Thank You!

A Computer name change broke my maintenance plans


I am using SQL Server 2008 (SP1) and Windows Server 2008


A Computer name change broke my maintenance plans




I have changed the name of the server (ie: Serv1-SQL to Serv2-SQL) and done the appropriate


EXEC sp_dropserver 'Serv1-SQL'


EXEC sp_addserver 'Serv2-SQL', local

SQL 2008 Maintenance plans missing Truncate transaction log checkbox



I am a noob SQL 2008 DBA and had a quick question on the maintenance plans. Under the database/tasks/backup function under options there is checkbox to truncate logs when they are being backed up. Yet when I create a transaction log backup job using the maintenance wizard the checkbox is missing for that option.  Why is that missing? 

Thanks, Jules

Missing Maintenance Plans


SQL Server 2008 R2 was installed by a vendor and when I go into SSMS I cannot see the Maintenance Plans Folder.  I have signed in as myself (sysadmin) and using the sa account and still cannot see the folder.  What could be the problem?


Maintenance Plans - Check Database Integrity Error

Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End Progress  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  9:04:40 AM  Finished: 9:04:41 AM  Elapsed:  0.842 seconds.  The package execution failed.  The step failed.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend