<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 7,2000
»
Administration
»
How to script existing DB Maintenance Plans
25 posts, Page 1 of 3
1
2
3
»
»»
How to script existing DB Maintenance Plans
Rate Topic
Display Mode
Topic Options
Author
Message
miodrag.sabljic
miodrag.sabljic
Posted Thursday, May 15, 2008 1:55 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, March 09, 2009 8:02 AM
Points: 3,
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?
Post #501078
ddonck
ddonck
Posted Wednesday, May 28, 2008 4:34 AM
SSC Rookie
Group: General Forum Members
Last Login: Thursday, April 18, 2013 3:06 AM
Points: 25,
Visits: 131
I'm looking for the same thing, only for MS SQL 2005. Has anyone got any ideas?
Post #507538
ddonck
ddonck
Posted Wednesday, May 28, 2008 5:27 AM
SSC Rookie
Group: General Forum Members
Last Login: Thursday, April 18, 2013 3:06 AM
Points: 25,
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.
Post #507575
tosscrosby
tosscrosby
Posted Wednesday, May 28, 2008 8:45 AM
SSCrazy
Group: General Forum Members
Last Login: Tuesday, April 19, 2011 7:31 AM
Points: 2,832,
Visits: 5,316
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.
Post #507728
ddonck
ddonck
Posted Friday, June 06, 2008 7:03 AM
SSC Rookie
Group: General Forum Members
Last Login: Thursday, April 18, 2013 3:06 AM
Points: 25,
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.
Post #512855
ddonck
ddonck
Posted Thursday, June 12, 2008 2:55 AM
SSC Rookie
Group: General Forum Members
Last Login: Thursday, April 18, 2013 3:06 AM
Points: 25,
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.
Post #515726
george sibbald
george sibbald
Posted Friday, June 13, 2008 7:19 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 5,264,
Visits: 11,191
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
---------------------------------------------------------------------
Post #516659
Anthony L. Thomas
Anthony L. Thomas
Posted Tuesday, July 22, 2008 12:05 PM
Old Hand
Group: General Forum Members
Last Login: Thursday, May 02, 2013 8:20 AM
Points: 354,
Visits: 65
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
Post #538711
george sibbald
george sibbald
Posted Tuesday, July 22, 2008 1:16 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 5,264,
Visits: 11,191
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?
---------------------------------------------------------------------
Post #538789
Anthony L. Thomas
Anthony L. Thomas
Posted Tuesday, July 22, 2008 3:45 PM
Old Hand
Group: General Forum Members
Last Login: Thursday, May 02, 2013 8:20 AM
Points: 354,
Visits: 65
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
Post #538879
« Prev Topic
|
Next Topic »
25 posts, Page 1 of 3
1
2
3
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
