SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to script existing DB Maintenance Plans


How to script existing DB Maintenance Plans

Author
Message
miodrag.sabljic
miodrag.sabljic
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 29
After installing of new SQL Server 2000 server I wolud like to avoid manual creating of DB Maintenance Plans that are created on another SQL Server 2000. Is it possible to do using any of the wizards that can generate SQL scripts or there is other way to move data into corresponding tables in msdb database?
ddonck
ddonck
Old Hand
Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)

Group: General Forum Members
Points: 349 Visits: 131
I'm looking for the same thing, only for MS SQL 2005. Has anyone got any ideas?
ddonck
ddonck
Old Hand
Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)

Group: General Forum Members
Points: 349 Visits: 131
Thought I'd just try to load a dump of msdb (from another MS SQL where I'd already created the Maintenance Plans) and this seems to work like a charm. I did have to reboot the MS SQL Server to be able to view and edit the Maintenance Plan tasks.
The only thing you might need to change is the databases the Transactionlog backups are made on (since "all databases" doesn't skip the "simple" databases, you'll have set specific databases) and directory locations.

This might actually work for MS SQL 2000 too, though you'd have to change some data in system tables as some of them contain the name of the MS SQL Server.
tosscrosby
  tosscrosby
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7950 Visits: 5316
You will have to update the table sysjobs in msdb. The field is originating_server and should reflect the new server name. I've done this in the past without issue.

Edit - This is for S2K.

-- You can't be late until you show up.
ddonck
ddonck
Old Hand
Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)

Group: General Forum Members
Points: 349 Visits: 131
There turns out to be something of a problem with loading a msdb backup (in MS SQL 2005). In Maintenance Plans the "Local server connection" is set to the original server and cannot be changed. You can ofcourse create a different connection (as we do) and point that to . (dot = local server). But it's stille quite confusing to have an Maintenance Plan in MS SQL Server A that has a local connection to MS SQL Server B.
Where it get's this from I do not yet know. It's not in any of msdb's tables or views. It might well be in one of the system tables, but as we're not allowed to query them anymore I don't know how to check (let alone change).

If anyone has any idea as how to solve this, I'd love to know.
ddonck
ddonck
Old Hand
Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)

Group: General Forum Members
Points: 349 Visits: 131
And one last update. Since I couldn't find a way to rename the local connection I decided to start fresh and delete the existing Maintenance Plans. That failed due to the local connection. I tried to hack my way through by deleting in the msdb tables, but that didn't work. And since I didn't make a backup of my original msdb after creation (I know, my bad), I was stuck.
To prevent a complete reinstall, I tried to load a backup of a freshly created msdb from another server, but this again gave problems. At the end I had to reinstall the complete MS SQL Server.

In short: I don't recommend anyone else try this.
george sibbald
george sibbald
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39192 Visits: 13702
ddonck (6/12/2008)
And one last update. Since I couldn't find a way to rename the local connection I decided to start fresh and delete the existing Maintenance Plans. That failed due to the local connection. I tried to hack my way through by deleting in the msdb tables, but that didn't work. And since I didn't make a backup of my original msdb after creation (I know, my bad), I was stuck.
To prevent a complete reinstall, I tried to load a backup of a freshly created msdb from another server, but this again gave problems. At the end I had to reinstall the complete MS SQL Server.

In short: I don't recommend anyone else try this.


looks like u have hit another reason why using SSIs for mantenance plans was not best idea in 2005 if you are a production DBA.

why use an integration tool to run intra server database maintenance tasks............ a bugbear of mine

---------------------------------------------------------------------
Anthony L. Thomas
Anthony L. Thomas
Mr or Mrs. 500
Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)

Group: General Forum Members
Points: 540 Visits: 71
You must keep in mind that only the SS2K maintenance plans are stored in the maintenance plans MSDB system tables as legacy object.

The new SS2K5 maintenance plans are modified SSIS (or DTS) packages and are stored in the dbo.sysdtspackages90 table, specifically in the Image column (why not VARBINARY(MAX)?).

Anyway, with the Business Intelligence Studio, you can add packages to projects, save them as files, or save copies to files, as SSIS package files, or, yes, back into the MSDB repository.

It is quite interesting to take a look at all of the "additional" items created for a maintenance plan package. But, one of those will be the connection manager objects, which contains the definition for the Local Server Connection object, and which can be modified.

Good luck.

Sincerely,


Anthony Thomas



george sibbald
george sibbald
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39192 Visits: 13702
Anthony question for you perhaps you can help with. If you can get maintenance plans working on a new server after copying over msdb, can you get all SSIS packages working which were saved to msdb?

---------------------------------------------------------------------
Anthony L. Thomas
Anthony L. Thomas
Mr or Mrs. 500
Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)

Group: General Forum Members
Points: 540 Visits: 71
First of all, DTS (and/or SSIS) packages are just that, packages. They can be saved to a code file, a proprietary file (.dts or .dtsx), or saved in the MSDB repository. When they are saved in the repository, they are stored as an IMAGE (or large binary) data type.

That is just storage. What the package does, how it executes, and the environment where it executes is up to you and the definitions within the package itself.

The tools outside of SQL Server help develop, deploy, and run the packages...regardless of where they are stored.

If you have restored a copy of an MSDB database from one platform or another, or exported out these image files and imported them to another system, you should still be able to read them with the native DTS, SSIS, BI Studio tools.

Keep in mind that SS2K packages are stored in MSDB.dbo.sysdtspackages, whereas SS2K5 packages are stored in MSDB.dbo.sysdtspackages90, and can only be read by the appropriate tools.

The SS2K5 tools can read SS2K packages, but only for execution, not for modification or upgrade.

Hope this helps.

Sincerely,


Anthony Thomas



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search