May 15, 2008 at 1:55 am
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?
May 28, 2008 at 4:34 am
I'm looking for the same thing, only for MS SQL 2005. Has anyone got any ideas?
May 28, 2008 at 5:27 am
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.
May 28, 2008 at 8:45 am
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.
June 6, 2008 at 7:03 am
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.
June 12, 2008 at 2:55 am
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.
June 13, 2008 at 7:19 am
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
---------------------------------------------------------------------
July 22, 2008 at 12:05 pm
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
July 22, 2008 at 1:16 pm
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?
---------------------------------------------------------------------
July 22, 2008 at 3:46 pm
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
July 22, 2008 at 4:13 pm
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.
---------------------------------------------------------------------
July 23, 2008 at 10:19 am
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
July 31, 2008 at 6:09 am
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
August 12, 2008 at 6:50 am
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.
August 19, 2008 at 1:39 am
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.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply