November 16, 2006 at 1:21 pm
Hi:
Want to copy DBs from SQL Server 2000 to 2005. any advice on which books, white papers, instructions, etc to do it step by step?.
DTS have some errors, and it does not work properly.
Any advice is appreciated.
Thanks
Pablo Campanini
November 16, 2006 at 1:25 pm
The two methods I have had success with are the detach/attach and backup/restore. I have not had any success with the Copy Database method using SQL Management Object method.
hth
November 17, 2006 at 2:11 am
I agree with the two methods. DTS will not work. If you are moving from one server to another, look for the new sp_help_revlogin to move logins.
moreno
November 17, 2006 at 4:19 am
The best way to move your database from SQL 2000 to 2005 is to script it out (schema and data) and then rebuild it from scripts in SQL 2005. Why?
The SQL Server 2005 Upgrade Advisor is limited
In theory, the SQL Server 2005 Upgrade Advisor should tell you about all the issues with your current database. In practice, it finds very little and tells you to check a whole heap of specific changes between SQL Server 2000 and 2005. Basically, SQL Server 2005 is a lot stricter than 2000 and I would be surprised if you did not have at least one SQL Server 2000 object that fails to compile in 2005. Checking manually for all possible issues would be time consuming and error prone, but there is a better way - using DB Ghost (www.dbghost.com).
DB Ghost can script out your entire database and rebuild it in SQL 2005. This will force each one of your objects to be built against a native 2005 database which has a compatibility level of 90 (more about that later). DB Ghost will list all the objects that failed to compile and these are the ones you need to fix.
The SQL Server 2005 compatibility level trap
Like previous upgrades to SQL Server, the latest version can support previous versions. This is done by setting a compatibility level for the database. SQL Server 2000 is compatibility level 80 and SQL Server 2005 is compatibility level 90.
If you restore a SQL Server 2000 backup, or use detach/attachto create a SQL Server 2005 database, the database will have a compatibility level of 80. You can then go to the database properties and change the level to 90. But here is the trap. When you upgrade the compatibility level SQL Server WILL NOT INFORM YOU ABOUT OBJECTS WHICH NO LONGER WORK UNDER SQL SERVER 2005.
For example, this is a code snippet from a stored procedure that compiles ok in SQL Server 2000 but fails in 2005:
CASE WHEN pir.PerformanceIndicator = 'E' OR.pir.PerformanceIndicator = 'A' THEN 1
Msg 4104, Level 16, State 1, Procedure prcPerformanceReportNavigator, Line 145
The multi-part identifier ".pir.PerformanceIndicatorResultStatusCd" could not be bound.
If the database was simply restored from the SQL 2000 backup (with compatibility level 80) and the compatibility level then set to 90 no errors are shown. However, if the stored procedure is executed it fails!
Msg 4104, Level 16, State 1, Procedure prcPerformanceReportNavigator, Line 144
The multi-part identifier ".pir.PerformanceIndicatorResultStatusCd" could not be bound.
Building your databases on a regular basis from scripts is good practice to ensure everything still hangs together properly as SQL Server simply does not maintain that kind of integrity for itself.
Malc
Malcolm
DB Ghost - Build, compare and synchronize from source control = Database Change Management for SQL Server
www.dbghost.com
November 17, 2006 at 5:36 pm
Excellent tip Malcolm! This is exactly what I'm doing to upgrade my 2000 database. I too found the Upgrade Advisor limited.
I'm a DB Ghost user and rely on it daily to manage my SQL Server 2000 database changes. It just seemed logical to do the same for my SQL Server 2005 database.
Martin
April 17, 2008 at 2:00 pm
Hi,
I do have a question related to SQL Server 2000 upgrading to 2005 by doing an inplace upgrade.
We do observed that the SQLS 2000 user database on the server as double its size :w00t: after the upgrade have been applied to the server.
In order to verified this, we did recreate a subset of tables and used INSERT ... SELECT * FROM ... and tables newly created went back to their original size.
Any reason why this occurred ? Any special things that upgrade can do to the data tables ?
Next time, we plan to do detach/attach with a side by side upgrade by installing SQLS 2005 instance on the same server as the SQLS 2000 instance.
April 18, 2008 at 8:04 am
I've had the best luck with the Backup/Restore method for upgrading 2000 to 2005, it has proven to be the simplest and most reliable method from my experience.
There should be links to the KB article on this method on this site, I know I've put them up a few times.
Good Luck!
April 18, 2008 at 8:17 am
Thanks for your comments.
But I am still waiting the answers to my questions
April 18, 2008 at 8:20 am
Did the .mdf grow or the .ldf? If it was the log file, it was probably due to the transactions required to populate the new instance.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply