Move DB from 2005 to 2000??
-
Hi,
Need some help in copying a DB from our Test/Development server (Sql server 2005 Dev) to our production server (Sql server 2000 Ent).
Have tried the obvious:
detaching, attaching. Error in sysindex.
Right-Click - Copy DB Wizard. Dest must be 2005.
Make DTS Package in 2000 to import from 2005, Can't , someting about wrong SMO
Make SSIS Package in 2005, Can't Source version must be less or equal to destination.
Please help. We have spend over 70 hours preparing the 2005 db, and need to put it in production now (2 days ago).
The DB is 4GB with more than 300 Tables, with 3-5 indexes on every table.
Need more info? feel free to ask.
/Weje
-
I was under the impression that SQL 2005 db could not go back to SQL 2000.. I am really not sure.. maybe you can do something at the table level? Maybe a two step process from sql2k5: BCP..OUT data for every table to a text file and then sql2k : BCP..IN from the text file to the 2000 tables?
Not sure it would work.. Wish I had something better.. good luck.
-
Once the DB touches 2005, it is 2005 and cannot be "un-upgraded" or "downgraded" to a previous version. The "upgrade" occurs when the DB is restored or attached to a SQL 2005 instance. The compatability level is still 80 (for 2000 DBs) upon upgrade. This means that SQL Server 2005 will "try" to behave like 2000 as best as it can, but there are some differences. Also, while the DB is in "80" compatability mode (BTW "90" is SQL 2005), the new features in SQL 2005 are not available. To see the compatability level of a database, use sp_dbcmptlevel 'dbname'.
Hope this helps!
-
What error did you get when trying the SSIS approach? Once the data gets into the pipeline the version of the source system is irrelevant so this can't be the problem that you were seeing.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
While I haven't tried this and i've encountered the same problem I wonder if you can right click the db in the object explorer and script the structure (table schema, indexes etc) to a script file and then execute that script file and build a new db on 2000.
This will give you the structure.
Then use 2000 import/export wizard (dts?) to get the data from 2005 and identity-insert it into the 2000 db. (maybe quicker to do bcp except i can't remember how to do this)
I think you should be able to downgrade but I think regardless of the solution you will need to rebuild the db as 2000 - at least this way it's semi automated.
-
I took great interest in this thread as I am faced with a similar situation - or at least I have been avoiding it and was wondering if I was avoiding it unnecessarily.... We still have many customers running SQL 2000 - most are gradually moving to 2005 but not straight away. Thus I am still developing against 2000 because I did not want to have the awkward process of ensuring any changes I make to the DB in 2005 are or are not compatible.
I agree with the others - you'll need to script out the tables, etc. If you have your DB in SQL 2000 compatibility mode then at least you shouldn't have any features on the tables (eg varchar(max) or XML columns) to get in your way. Then run the scripts against SQL 2000. Then bcp out the data and bcp it back in - you may need to use the text format of bcp rather than the native format (the "native" format may be different between 2000 & 2005).
Shame there isn't a friendly wizard for this
A quick google search has others in a similar situation receiving similar answers - script tables and bcp data. -
What do you mean by BCP data out from 2005?
-
bcp = bulk copy program. There's a command-line app shipped with SQL called bcp.exe. It lets you dump the contents of a table to a disk file and also lets you copy such a disk file into a table.
-
Well guys, I'm working on the same issue, I've tried moving the data from each table into a text file then importing those files into a 2000 db, I was able to load the files I tested but I lost the indexes and constraints. tried scripting my 2005 db and running the script in a 2000 new db, got many errors, some of them has to do with undefined index, unknown constraint, cannot build table relation ... etc; then I tried SSIS from 2005 and DTS from 2000,
unfortunately both failed without any good result; I'm in the process of copying 2005 db tables to MS Access database and then importing it into a 2000 db, I'm optemistic that this will work ... hopefully 
... I'll keep you guys updated ... -
hi friends,
u cannot downgrade ur box from a higher version to a lower version..........u cant restore 2005 db to sql server 2000......there is no way to downgrade it to lower version........also as mentioned by some1 the compatability issue of 90 in 2k5 and 80 in 2k will daunt the task.........refer the below link for compatability features comparision b/w 2000 and 2k5
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-48ebca266659.htm
[font="Verdana"]- Deepak[/font]
-
Just to let everybody know that I was able to load all tables from 2005 to 2000, I was able to move all tables' structure and 90% of the data then I used Red-Gate tool to do data comparison to move the missing data; YES it was not that easy but in the end we got all data from 2005 to 2000; I wish there was an easier way to do that ... I hope in the next version of SQL server we will have new features that allow us to do such thing ... "hopefully"
-
Just as a note - In sql management studio 2008 (katmai) you could go to: Tools > Options > Scripting > and set the Script for Server version to 2000.
Script out all your objects.
Run the scripts on your 2000 server database.
Then port the data from the 2005 database to the 2000 database
-
As others have said, and I have heard personally from the lips of Kimberly Tripp (works for Microsoft on the SQL Server team), you can NOT downgrade from SQL Server 2005 to SQL Server 2000. Even if you script out the tables from 2005, I doubt you can. There are too many little changes to the way data types / indexes / etc. are handled.
For instance, some data types don't exist in SQL 2000. Some of the SQL 2000 code doesn't exist in SQL 2005 anymore. Stuff like that.
As far as a data transfer goes, I doubt you can do it directly from 2005 to 2000. More like you'll have to create the scripts for the tables & indexes manually, run them in SQL 2000, then bcp out the data from SQL 2005 to a csv or other flat file and finally bcp the data back to SQL 2000.
Lastly, If you have a problem with clients who have not upgraded to SQL 2005, why are you developing in 2005?
You can install (or reinstall) SQL 2000 as a side-by-side install in a separate folder on your dev box (or PC) after 2005 has been installed. That's what we've done. This way we can develop 2000 stuff in 2000 without running into problems and move it to the 2000 production environment seamlessly. And we can always transfer the SQL 2000 development scripts to 2005 (assuming we didn't include anything that support has been killed for) without too much extra effort.
Seems to me you're working in the wrong direction.
-
I agree with Brandie. You should have dev/qa/production the same version.
If you run production on SQL 2000, make the database work and verified on a SQL 2000 environment first before promoting to production.
-
Quest Software has a free schema / data comparison tool that makes a script that is SQL 2000 compatible. So, if you compare a SQL 2005 database to a SQL 2000 database with no objects, it will script everything (even the data if necessary) and allow you to re-create the database in a SQL 2000 environment. I have never actually used the tool to do this, but since it is a free tool, trying it is pretty low-risk.
Being that we are in this particular forum, I would also suggest you check out the RegGate database comparison tools as they will probably also be able to do this for you. Their tool is probably better, but it is not free. Of course, in the world of software, you usually get what you pay for.
It is not a great solution, but if you have to un-upgrade a database in a pinch, it is better than most other options.
Obviously, if you have used SQL 2005 datatypes or other features, you may find yourself out of luck.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply