How to script existing DB Maintenance Plans

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » How to script existing DB Maintenance Plans

25 posts, Page 2 of 3

««1 2 3 »»

How to script existing DB Maintenance Plans

Rate Topic

Display Mode

Topic Options

Author

Message

george sibbald

Posted Tuesday, July 22, 2008 4:13 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 5,264, Visits: 11,191

Anthony,
thanks for that, most helpful. so what it means is in 2005 we can still use the DR process of copying over and restoring the msdb to recover jobs, alerts and packages. However whereas in 2000 all we had to do was update sysjobs.originating_server witht the new server name, we will now also need to go through the SSIS packages in BIDS and update the connection manager objects, which could be time consuming if you have lots of packages, unlesss that can be automated.

---------------------------------------------------------------------

Post #538884

Anthony L. Thomas

Posted Wednesday, July 23, 2008 10:19 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 02, 2013 8:20 AM
Points: 354, Visits: 65

Basically, yes, but even in SS2K you might have needed to use the DTS Designer to modify package connections if you used "(local)" and/or logged package executions locally.

It is a little more complicated because the 9.0 SSIS packages can be more complex, but the idea is generally the same.

I certainly would not try upgrading an SS2K MSDB to SS2K5 and migrating platforms to boot. I'd upgrade the database first, and then try to relocate a copy.

Sincerely,

Anthony Thomas

Post #539451

ddonck

Posted Thursday, July 31, 2008 6:09 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 18, 2013 3:06 AM
Points: 25, Visits: 131

Hello Anthony,

Thanks for the info, but how do I open an existing Maintenance Plan in the Business Intelligence Development Studio? From what I see I can't log into the MS SQL Server to go to the msdb table.

Debora

Post #544305

ddonck

Posted Tuesday, August 12, 2008 6:50 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 18, 2013 3:06 AM
Points: 25, Visits: 131

I've been able to get there (on MS SQL Server 2005), with thanks to directions at http://sqlserver-qa.net/blogs/tools/archive/2007/09/09/value-of-9-6-2007-12-00-00-am-is-not-valid-for-value-value-should-be-between-mindate-and-maxdate.aspx about how to open, save and modify existing Maintenance Plans.

It's not as straight forward as I'd like. I still have to put some effort into changing connections and due to having named instances, we have to change MsDtsSrvr.ini.xml.

It does all work and seems to be less work than creating the Maintenance Plans manually, manely due to our preferred connection settings in the Maintenance Plans. But it is a bit of a hassle that you cannot just use one product to do it all. And I do wonder if I could get this to work if I had two named instances on the same server. I'm not sure how one would change MsDtsSrvr.ini.xml for that.

If anyone would like a detailed description of how I got it working, let me know and I'll post it. Or mail me directly.

Post #550929

Sean Kohler

Posted Tuesday, August 19, 2008 1:39 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, June 15, 2010 1:16 AM
Points: 7, Visits: 28

Don't know how relevant this thread is anymore, but I was able to move a 2K5 maintenance plan by exporting it in SSIS. After the export, I had to open up the resultant dtsx file in Visual Studio and change the global path variable for reporting to a path on the new server, change the report task path property to the same location, and update my connection configuration. Resaved the file with the new path and connection manager value (I was moving from a cluster instance to a standalone, so had to define localhost). Imported into the new sql server and set up the schedule...

Worked fine.

I can give further detail on the process if needed.

Post #554781

BCLynch

Posted Wednesday, October 08, 2008 9:43 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:16 AM
Points: 21, Visits: 364

Hi there
I took a maintenance plan (conencted to SSIS) end exported it to a file. I connected to a clean sql server and imported the dtsx file.
This created the maintenance plan object, but it did not create any of the related jobs under SQL agent.
Upon looking at the maintenance plan I could see each of the subtasks marked as unshceduled.
I manually rescheduled one of the tasks and magically all the agents jobs now reappeared. None of the other tasks changed from unshceduled

My questions are
Why did the jobs magically appear only when I manually rescheduled one of them/
How can I relink the jobs created by importing the dtsx maintenance plan to its jobs?
and
Why is all of this such a pain in the ***?

Thanks
Barry

Post #582716

george sibbald

Posted Wednesday, October 08, 2008 10:15 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 5,264, Visits: 11,191

BCLynch (10/8/2008)

I wish I knew the answer to your questions. As to why it is such a pain I think this is a black hole in DR procedures microsoft didn't see ( or would not acknowledge) when SSIS was used for maintenance plans and SSIS seperated out to use the BIDS interface. We production DBAs always seem to get the dirty end of the stick.

It is sad there are no MS whitepapers on the subject of migrating the msdb database (that I can find). mind you, I'm not sure the SQL2000 quick fix of updating sysjobs.originating_server was advertised on official MS sites.

End of Rant.

---------------------------------------------------------------------

Post #582743

ddonck

Posted Wednesday, October 08, 2008 2:00 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 18, 2013 3:06 AM
Points: 25, Visits: 131

I was able to get the jobs to appear by stopping and starting one of the services. Can't remember which one right now and can't check it as I'm at home.

Post #582893

Anthony L. Thomas

Posted Thursday, October 09, 2008 6:02 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 02, 2013 8:20 AM
Points: 354, Visits: 65

These difficulties are due to the need for backwards compatibility and the new architecture. If you examine an SS2K5 (or SS2K8, for that matter) MSDB database, you will notice the original db_maint... tables that support the old SS2K maintenance plans and architecture.

You will also notice two sets of dts... type tables (dts... and dts...90). These are to support both the old SS2K and new SSIS packages.

Even in SS2K, the data between the db_maint tables and SQL Agent job tables were linked, but only parsed by the Database Maintenance Wizard.

The same is true for SS2K5 (only parsed by the Wizard); however, the database maintenance tasks are now defined through SSIS packages and stored in BLOBs within the dts...90 tables.

So, the jobs are not scanned (or reconciled) until the Database Maintenance Wizard parses them out.

You must keep in mind that the BIDS interface is NOT the DBMW; however, BIDS can connect to a SQL Server repository to extract dts table packages, and then edit them through that interface. BIDS can then "Save As" a new dts table package (The Repository) on the same SQL Server instance, another instance on the same host, or a remote instance, just like the DTS Designer version 8.0 (SS2K). But, because BIDS is NOT DBMW, the jobs are not reconciled until opened by the later interface once BIDS has created the new package.

Hope this help clarify some of the mechanisms the SQL Server tools are employing.

Keep in mind that prior to SP2 (or was it SP1), there was no DBMW interface and all maintenance plans had to be administrated through SSIS and BIDS. So, the situation is improving. Take note that this appears to be the design direction, to elminate the DBMW and force adoption of SSIS entirely (maybe...)

Sincerely,

Anthony Thomas

Post #583227

BCLynch

Posted Thursday, October 09, 2008 8:32 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:16 AM
Points: 21, Visits: 364

That's interesting (and detailed) commentary. Not in a position to test right now so I'll ask you. No doubt like a lot of people here I'm looking to identify a technique to "automate" (i.e minimise manual intervention/reconfiguration) the deployment of an existing MP and related tasks to multiple servers.
Are you saying that I could
1. Create MP1 and it derived scheduled jobs on Server A
1A. Use SSIS to export MP1 to a DTSx file inthe file system
2. Use BIDS to open the MP1 dtsx file
2a Save the MP1 file (now open in BIDS) to Server 2
3. Somehow (there's the rub) use the MPW on Server 2 to reparse MP1 on Server 2??

I appreciate now that the SSIS dtsx export feature is to allow editing in BIDS rather than, as I assumed, to allow importation into a different SQL Server - a mechanism to migrate MPS and related jobs. I think they should actually have blocked SQL import of the DTSX because all it does is cause confusion when it doesn't emerge fully formed after the import. Then again maybe I should have read more and assumed less Smile

Post #583360

« Prev Topic | Next Topic »

25 posts, Page 2 of 3

««1 2 3 »»

Permissions