SQL 2005 -> SQL 2000

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Ree
Forum Regular
Posts: 592
Joined: Fri Jun 10, 2005 1:43 am
Location: LT

SQL 2005 -> SQL 2000

Post by Ree »

How can I export my database from SQL Server 2005 to SQL Server 2000? Restoring .bak, does not work. I'm lost here.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

I believe there was a migration kit developed by Microsoft... 'Data Transformation Services' or something sounding similar.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

If you have enterprise manager you can use DTS as weirdan suggested. Otherwise, you will need a conversion kit of some sort. But I know that enterprise manager can DTS just about any RDBMS to just about any other DB format.
Ree
Forum Regular
Posts: 592
Joined: Fri Jun 10, 2005 1:43 am
Location: LT

Post by Ree »

Who needs to have the tools - me or the SQL 2000 (my host) guys? My db is created and stored in SQL Server Express 2005 and it does not come with any tools of similar sort. Can the host import the data with DTS?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Can the host import the data with DTS?
Ask them :)
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Sorry, SQL Server (the commercial edition) comes with Enterprise Manager. If you had that, you could DTS the information yourself.

How are you connecting to your hosted DB?
Ree
Forum Regular
Posts: 592
Joined: Fri Jun 10, 2005 1:43 am
Location: LT

Post by Ree »

I am losing hope.

The only solution I know is connecting from my computer to the SQL 2000 server remotely and recreating the db there. What tool would allow me to do this? I can't find anything for this purpose.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

I would google the term 'SQL Server Express DTS tool' or something along those lines. What you want is a Data Transformation Service application so you can talk to another DB from yours.
Ree
Forum Regular
Posts: 592
Joined: Fri Jun 10, 2005 1:43 am
Location: LT

Post by Ree »

Well, I can connect to the MSSQL 2000 server from MS SQL Server Management Studio Express, and I can see and create tables (so basically I can view both db's side by side). Is there a way to copy-paste the tables from my db to the SQL 2000 db? Drag and drop doesn't work.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Can you do a table dump as a SQL file and load that file into your hosted db?
Ree
Forum Regular
Posts: 592
Joined: Fri Jun 10, 2005 1:43 am
Location: LT

Post by Ree »

You mean scripting the SQL 2005 db? I let it generate the db object script and then executed it on an empty db in SQL 2000. There were some incompatibilities, but I corrected those manually (I think it was because of collation difference) and now I have the db table structure created. Is there an easy way to dump the data from the tables into txts so that I could send them to my host to import? I don't want to copy-paste table data of ~30 tables.
Ree
Forum Regular
Posts: 592
Joined: Fri Jun 10, 2005 1:43 am
Location: LT

Post by Ree »

Damn, when I copy-paste the data manualy from SQL 2005 table to SQL 2000 table, my identities (primary/foreign keys) get messed up and the correct relationship is not maintained among tables. Any solutions on how to export all the data in my database and then import it back into my host's db so that identity relationships are not lost?
JackV
Forum Newbie
Posts: 6
Joined: Sat Sep 22, 2007 9:26 am

Post by JackV »

If you can manage to get your hands on the SQL Server Management Studio (full version) there is a good export wizard
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Yet another old thread, this time over a year old, that you've woken back up JackV...
Post Reply