October 26, 2011 at 9:44 am
We are in the process of moving our SQL 2005 environment to SQL 2008 in brand new boxes; we are planning to do a restore of full backups to copy the DBs, but one of our main concerns is what the best approach is to move the information in MSDB (jobs, SRS scheduled subscriptions, SSIS packages, etc) from SQL 2005 to SQL 2008.
- Can we do a restore of MSDB?
- Should we just script all jobs including SRS?
Any ideas would be gratly appreciated.
October 26, 2011 at 10:01 am
You can't restore msdb between versions. Your best bet is to script jobs, upgrading SSIS packages in BIDS, and recreating maintenance plans.
Greg
October 26, 2011 at 10:20 am
In the strongest terms I would not recommed trying to do a restore of an previous version to a newer version of ANY (ANY!) system databases, which I count msdb as. Your best bet is to script out the jobs and rebuild them on the new server.
CEWII
November 2, 2011 at 11:17 am
Soon, I will also be moving a SQL 2005 environment to SQL 2008 on a new box.
What if the 2005 instance was ugraded to SQL 2008? Then msdb could be backed up and restored to the new box.
Thoughts?
November 2, 2011 at 1:23 pm
You are asking for trouble, if the server was in-place upgraded you MIGHT get away with it. But I've got to say I wouldn't do it. Why are you against scripting the objects out and recreating them? I know its a pain but it is at least safe.
CEWII
November 2, 2011 at 1:35 pm
If the databases are at the same version, it is feasible.
However, I prefer to restore the database as a different database name, then import the data that I need.
Or I will script the necessaries and run the scripts against the new MSDB database.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 2, 2011 at 3:05 pm
Elliott Whitlow (11/2/2011)
You are asking for trouble...CEWII
Why? Can you give me some specifics?
November 2, 2011 at 3:09 pm
Dave Mason (11/2/2011)
Elliott Whitlow (11/2/2011)
You are asking for trouble...CEWII
Why? Can you give me some specifics?
I think he was understanding you to say restore the database from 2005 to 2008. If you upgrade on one machine first and then restore to the same version - the concerns become considerably less.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 2, 2011 at 3:13 pm
It seems like when I script out objects and recreate them on another server, I always forget something. :blush:
FWIW, I've taken backups of msdb and master and restored them to another instance (SQL 2005, though). This was for DR testing purposes. As I recall, one of the caveats is that the sql version number must be identical between the instances. The same version requirement was for master--I'm not sure if it applies to msdb too. As a result, all objects were present with no need for scripting. This seemed like a more thorough approach.
November 2, 2011 at 3:13 pm
In every version structures are either added or changed, code is removed, added, changed, and the agent is expecting certain code and structures.
In most cases a service pack won't fix this because it has certain expectations about structures as well, such as if the version is RTM then the structure will be like Y, but lets say you bring a 2005 msdb over, how do you intend to upgrade the structures?
While I said you might be able to get away with bringing over a msdb that you performed an inplace upgrade on I still find it troubling. In all the cases where I have done upgrades I have treated the time as a point to cleanup old stuff and ONLY bring over what we actually needed. I didn't bring over 1000 DTS jobs when I needed only 5 because they did a poor job of cleanup in the past before me. Or all those jobs that were disabled but never deleted.
Me, I wouldn't do it, you are free to do it but if you ever need support from MS for agent and you describe where msdb came from I'm thinking you might have a problem.
CEWII
November 3, 2011 at 7:19 am
A SQL2005 version of master and MSDB won't work in a SQL2008 install via the backup/restore method. Microsoft can change the structures in both and even if you do that and SQL Server starts I would not recommend running it that way. The first time you apply a SP or CU I am sure it will fail.
You HAVE to script out what you have and import that data into the 2008 version.
November 3, 2011 at 9:02 am
Dave Mason (11/2/2011)
Elliott Whitlow (11/2/2011)
You are asking for trouble...CEWII
Why? Can you give me some specifics?
maintenance plans will still point to the old server - and try to run against the old server, have to be manually removed
SSIS packages or SQLagent jobs with server name hardcoding will need to be amended - so you may as well have scripted them out anyway
Proxy accounts will be orphaned from the credential and have to be recreated - this can only be done after all jobs using the proxy are dropped - so again you have to script them out
Internal SQL accounts created at install time will also be orphaned.
If you use database mail msdb will have to be re-enabled for service broker.
is that enough 🙂
I wonder if MS considered DR when this was all designed.........
---------------------------------------------------------------------
November 4, 2011 at 8:08 am
Markus (11/3/2011)
A SQL2005 version of master and MSDB won't work in a SQL2008 install via the backup/restore method.
Agreed! That is why I posed the following question in my previous post:
"What if the 2005 instance was ugraded to SQL 2008? Then msdb could be backed up and restored to the new box."
November 4, 2011 at 8:29 am
Dave,
It sounds like you really want to do this. You might be able to get away with it if you do an inplace upgrade on the existing one, but it is not recommended and personally I wouldn't do it. If I had no choices I might consider it, but that is not the case here. There are a number of places that have to be tweaked for a new server name name, and I only know a few of them. In addition to what will be broken like maintenance plans.
Good luck.
CEWII
November 4, 2011 at 8:37 am
Dave Mason (11/4/2011)
Markus (11/3/2011)
A SQL2005 version of master and MSDB won't work in a SQL2008 install via the backup/restore method.Agreed! That is why I posed the following question in my previous post:
"What if the 2005 instance was ugraded to SQL 2008? Then msdb could be backed up and restored to the new box."
you could do this Dave and it would restore but you would face the issues I described above, it really depends how complex your environment is and what you think you would have to amend in msdb after the restore. If all you have is some SQLagent jobs and a few packages you would be better off scripting these out and piping in to the new msdb, and creating any maintenance plans from scratch.
---------------------------------------------------------------------
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply