February 16, 2008 at 6:17 pm
Tried copying a database from 2000 to 2005 and this is what I get.
I have 2000 and 2005 on the same box, the agent service accounts are the same, the SA password is the same, no issues with the disk-space.
Here is the error message::
:w00t:
could not load type 'microsoft.sqlserver.management.smo.agent.job basecollection' from assembly 'microsoft.sqlserver.smo, version=9.0.242.0, cluture=neutral, publickkeytoken=89845...
any information will help.
thx
John Esraelo
Cheers,
John Esraelo
February 16, 2008 at 8:19 pm
What service pack level of 2005 are you running?
In any case, why not just detach the database from 2000 and then do an attach for 2005? If you're using the wizard (you don't say you are but I assume that's where the error's occurring) then just do it manually instead.
February 16, 2008 at 8:32 pm
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Cheers,
John Esraelo
February 17, 2008 at 4:01 am
Hi John,
Ensure that you have applied Sql Server 2005 SP2 for Tools as well.
Refer this http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1404000&SiteID=1
for more information.
[font="Verdana"]- Deepak[/font]
February 17, 2008 at 10:50 pm
I find that the easiest and quickest way to upgrade a db from 2000 to 2005 if simply to restore a backup of the 2000 database to the 2005 instance and then just change the database compatibility option.
February 18, 2008 at 8:19 pm
the restore option of 2000 in 2005 sounds real simple; however, the stored procedures in this database use dtproperties and sysusers, etc.. would those tables also get crossed over or somehow fused with the 2005 system tables / stored procedures?
John Esraelo
Cheers,
John Esraelo
February 18, 2008 at 8:41 pm
dtproperties and sysusers are both system tables that are stored in the local database and in the master database. Because they are stored in the local database they would be restored as part of the db restore. Anything that is NOT in the local database, for instance things that are listed in the master database would NOT be restored.
This is typical and will be the same even in you do an SSIS package transfer. I recommend to make sure that all the users/priveleges are properly set up before you do the restore. The restore will work either way, but you will have issues using database objects that are assigned to users that haven't been set up in the master database.
Just to recap, the copies of dtproperties and sysusers that are local to the db you are restoring will get restored successfuly. The copies of dtproperties and sysusers that are local to the master database will NOT be restored.
So it will work, it is easy, just make sure that users and priveleges are set up in advance.
-Danny
February 18, 2008 at 8:50 pm
The 2005 instance is maintain seperately from the 2000 instance. Restoring the database in SQL 2005 will not effect any of your 2000 code or objects. You may have problems if you have bad programming logic in your code such as *= or order by problems. I would suggest you run the upgrade advisor to confirm that your code is valid. The SQL 2005 is much stricter on sytax and code logic. You can run the database in compatability level 80, but you lose new functionality.
Also note that sysusers is going to be removed from future releases of SQL Server. This does include SQL 2008 (I believe). Additionally, I do not believe dtproperties is valid in SQL 2005; all diagrams are use sysdiagrams.
A list of 2000 system tables that have been modified to use system views in 2005.
February 18, 2008 at 9:42 pm
Danny, I was hoping that would work, here is what I did during the last 40 minutes.
1> backed up and restored the 2000 in 2005 {no go}
2> detached 2000 and reattached with 8.0 compatibility {no go}
for some reason they don't come across / over...
I am about to read what Adam has for me..
thank you though
I can use all the help I can get and the man knows that...
John Esraelo
Cheers,
John Esraelo
February 18, 2008 at 9:46 pm
Adam, you are absolutely right.
I will have to either keep the 2000 for my business sake or change my iBuySpy Store {shopping cart app} to work with the 2005.
I have to make that decision now.
Thank you Adam
Thx guys..
John Esraelo
😉
Cheers,
John Esraelo
March 1, 2008 at 1:25 pm
Have you attempted to install an identical SQL 2000 instance and try an in-place upgrade? It may be to your advantage to see if SQL 2005 can convert an existing, functional SQL 2000 instance, user and system databases. Once fully converted, you can drop the databases and reattach them in a cleanly installed SQL 2005 instance.
March 4, 2008 at 11:24 am
I am in the process of migrating a 68gb database from 2000 to 2005. I plan on using the "copy database" method and have produced the SSIS package.
Here are my issues:
1) I need to know what kind of data transfer rate to expect. I only have a maint. window of 4 hours.
2) is there a way to devise the package to stop and start? Several of the tables are over 8gb, 1 containing over 2.3m rows and the other almost 8m rows. Both tables have ntext fields in them as well. If I had a way to construct the transfer so that I can allow this copy to take place over several evenings to accomplish the work would be a great thing.
3) I cannot afford to use the "detach/attach" method because the source database IS a production database and cannot have the database off-line. It gets accessed 24/7.
4) I tried to run a backup on this database and found it would take more than 4 hours to perform.
5) Is there another method I could use to migrate the database
a) with low risk ?
b) perform in a single 4 hour window?
Any advice would be greatly appreciative.
Kurt
DBA
RHWI, Inc
Poughkeepsie, NY
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply