February 15, 2011 at 7:21 am
We are planning to move from 2005 to 2008 SQl Server (different/newer server). Our plan is to do full backup of all user databases from 2005 and restore them to 2008. We have completed multiple checks to verify the conversion will take place with minimum issues as the databases are relatively simple.
I believe we should do the same for the Master and MSDB databases, but would appreciate comments if this is not an appropriate plan.
February 15, 2011 at 9:54 am
not sure if you need to move the master database
moving to sql2008 from sql2005 isnt very difficult
the issue is there isnt any going back since you cant restore sql2008 to sql2005
didnt say if you are doing standard to standard
or ent to ent
I have had issues with some deprecated items but that usually can be handle with compatibility
February 15, 2011 at 10:12 am
Thanks for your answers. We are moving enterprise to enterprise. However, the current server actually uses little other than what would be considered standard.
February 15, 2011 at 10:19 am
Howard C. BAchtel-438731 (2/15/2011)
We are planning to move from 2005 to 2008 SQl Server (different/newer server). Our plan is to do full backup of all user databases from 2005 and restore them to 2008. We have completed multiple checks to verify the conversion will take place with minimum issues as the databases are relatively simple.I believe we should do the same for the Master and MSDB databases, but would appreciate comments if this is not an appropriate plan.
I don't think you can restore a 2005 backup of a system database to SQL Server 2008, and even if it will let you, it will probably cause severe problems.
February 15, 2011 at 11:40 am
Howard C. BAchtel-438731 (2/15/2011)
We are planning to move from 2005 to 2008 SQl Server (different/newer server). Our plan is to do full backup of all user databases from 2005 and restore them to 2008. We have completed multiple checks to verify the conversion will take place with minimum issues as the databases are relatively simple.I believe we should do the same for the Master and MSDB databases, but would appreciate comments if this is not an appropriate plan.
You should be able to restore and use your databases without any issues. I believe they will still be in compatibility level 90. You can't move the SQL 2005 system database to 2008. If you want to move msdb to preserve jobs, etc, you can script them out in 2005 and run the scripts in 2008.
February 15, 2011 at 11:42 am
I was wondering about the restore of the master/msdb (msdb mostly). Would I need to regenerate the Agent jobs and SSIS packages loaded from SSIS for the 2008 version? I have the documentation that I can do this from and the source code for all scripts, including SSIS packages, is stored off server so it could be done with basically routine loads.
If that would be the better way I can do that.
February 15, 2011 at 12:44 pm
Thanks for the ideas about scripting out the jobs. Would I be able to do the same for logins, Linked Servers and other objects that are not database oriented?
February 15, 2011 at 2:04 pm
You cannot restore system databases from a different edition. You will need to script out the agent jobs, logins, etc...
You can download and use the sp_help_revlogin procedure to move logins from one server to another. I don't have the link handy, google it and you will find it quickly enough.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply