Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

How to script existing DB Maintenance Plans Expand / Collapse
Author
Message
Posted Thursday, May 15, 2008 1:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 9, 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
Posted Wednesday, May 28, 2008 4:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, May 28, 2008 5:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, May 28, 2008 8:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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
Posted Friday, June 6, 2008 7:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, June 12, 2008 2:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Friday, June 13, 2008 7:19 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:27 PM
Points: 5,974, Visits: 12,875
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
Posted Tuesday, July 22, 2008 12:05 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 7:59 AM
Points: 354, Visits: 67
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
Posted Tuesday, July 22, 2008 1:16 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:27 PM
Points: 5,974, Visits: 12,875
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
Posted Tuesday, July 22, 2008 3:45 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 7:59 AM
Points: 354, Visits: 67
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 »

Add to briefcase 123»»»

Permissions Expand / Collapse