January 3, 2008 at 3:19 pm
I am attempting to copy SQL 2000 Database to SQL 2005 Database.
ok, does anybody know the impact of setting the database from "Version80 database compatibility level is
not supported." to Version90? I am trying to copy SQL 2000 database to SQL 2005. I need to copy
data on a regular basis. I dont want to shoot myself in the foot, and cause stuff to break if I update
SQL 2000 version90. Any input will be VERY welcome.
I know about the command EXEC sp_dbcmptlevel DatabaseName, 90
(
-Set the database to single user access mode by using ALTER DATABASE SET SINGLE_USER.
-Change the compatibility level of the database.
-Put the database in multiuser access mode by using ALTER DATABASE SET MULTI_USER.
)
I just dont know about the impact. I have 43 websites that hit this database.
I just need to find out what will break, OR MIGHT break by doing this.
:unsure:
January 7, 2008 at 10:47 am
ok, nobody has responded, so I thought I would add to this pain in my ..... I have 2 dev servers, one is a 2000 server, another is 2005 server. I successfully downgraded the 2005 server to level 80, but I still continue to get the same problems.
I attempted this ....
Try changing the provider to Microsoft OLE DB Provider for SQL server. The default provider probably does not work for SQL server 2000, without any luck.
I tried to create a DTS package to connect to 2005 to 2000. It won't even do that. not surprising.
I would appreciate any suggestions at all, as I am at high levels of frustration at this point
January 7, 2008 at 5:10 pm
If your goal is to migrate a database from SQL 2000 to SQL 2005, I suggest using either the backup/restore or detach/attach method. I've never been a big fan of the Copy Database Wizard or using DTS to move entire databases.
By default, SQL 2000 databases attached or restored in a SQL 2005 instance retain compatibility level 80. They'll work fine although new features won't be available. Is your regular data copy coming from SQL 2000?
Greg
Greg
January 9, 2008 at 9:24 am
I just wanted to copy the database to another server on a regular basis because right now SQL 2000 DTS packages are loading the data. I need to keep the latest data on the 2005 database for the development efforts to convert everything and some of the testing efforts.
so right now no one can tell me how to copy 2000 to 2005 db using sql objects task? :crying:
January 20, 2009 at 9:04 am
http://msdn.microsoft.com/en-us/library/ms178653(SQL.90).aspx
details what the impact of changing the compatibilty level would be. You need to examine your web apps to see if they will be affected by the changes listed in the document.
As for copying data between 2000 & 2005 could you tell me what errors you are getting ?
Just because you have DTS's doesn't mean you can't schedule backups/restores from 2000 to 2005. In some cases a full restore is actually quicker!
January 20, 2009 at 9:54 am
a full backup and restore it the only working option.
A backup and restore wipes out too many things. permissions and so forth.
I had a need to copy data from 2000 to 2005 tables are a regular basis, not the entire database (anymore). I finally wrote a program to do it myself. Microsoft really messed up on this part of 2005.
November 23, 2009 at 3:23 pm
Try installing the Backward Compatability Pack on your 2005 instance. That should fix the issue i think
March 7, 2010 at 3:45 am
Change to compatibility level of the destination master database to the appropriate level (i.e. 90 for 2005, 100 for 2008).
Probably the destination has been upgraded, and in this situation master remains in the compatibility level of the previous version, assuming some user objects (sp, funcs) exist there.
Make sure though that you don't have any code objects in master that could be affected by upgrading the compatibility level. In most of the cases there is not any problem.
After this changing, your Copy DB task will succeed.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply