June 9, 2009 at 11:40 pm
Hi:
I have several SQL Server 7 instances running on Windows Server 2000 and I need to upgrade all these instances and databases (including the master, msdb, etc) to SQL Server 2008 (on new Windows 2008 machines). Several of the Sql server 7 databases have compatablity-with-6.5 ON.
After some studies on the net and trial tests on VM machines, it seems that I need to do a 2-step upgrade (Sql server 7 to Sql server 2005, then to Sql server 2008) and I also encountered some issue with those databases with compatability-6.5 on (e.g. restore from sql 7 to sql 2005 did not work).
As a newbie, any suggestions on the best way for the migration is much appreciated.
Regards
June 11, 2009 at 7:01 am
I see that you have worked on virtual machines.
This is good way to keep your original data safe and does not require you database server installations.
For migration, you can backup and restore your databases on newer SQL Server.
Otherwise, you will be upgrading your database servers in place.
It will take some time but better to migrate SQL2k to SQL2005 then to SQL2008, etc.
Eralper
June 30, 2009 at 10:45 am
First of all SQL 2008 does not support 6.5 compatibility, only 7, 2000, and 2005. I'm not surprised that a db with 6.5 compatibility failed, SQL doesn't know what to do with it.
Also I would run the Upgrade Advisor from the SQL 2008 Feature Pack page, the download is here:
http://go.microsoft.com/fwlink/?LinkId=147019
It should give you a lot of information as to what it sees as the problems. Also, I would be very careful about upgrading master and msdb, the easiest way is probably to script out what you need from them..
CEWII
July 1, 2009 at 2:45 am
You say you will be upgrading on to new machines. In this situation you cannot upgrade your old system databases, you must use the new system databases installed with SQL Server 2005 or 2008.
Even if you think you can find a way to upgrade the system databases, I would advise against this. You would be relying on Microsoft getting all their upgrade scripts right, which is a very brave assumption.
You should plan to script out all objects from your system databases that you want to put into the new SQL instance. The FineBuild product on Codeplex has scripts that can deal with all SQL 2000 objects, and are probably fine also for SQL7. Google can find you lots of other scripts if you need them.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 1, 2009 at 7:22 am
I think Ed is right on, there are almost no good reasons to try to upgrade master and msdb, with that said, there are probably things in those databases you might like to carry forward, scripting is a great way to do that and you only need to do it on those databases..
CEWII
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply