Web Hosting Talk : Web Hosting Main Forums : Web Hosting : Windows Hosting : Converting MS SQL Server 2005 DB to 2000

[ThunderCloud]

I recently signed up with cartikahosting.com only to find out they do not support SQL server 2005 when I tried to install my application.

Their features and price are excellent so I would like to stay with cartika if possible, is there a way to convert my database to 2000? Or backup the database using sql server scripts?

[plumsauce]

Never tried a downward migration.

BUT.

To mount a db, the sql server must have been installed with the same language and sort orders that the db was originally created with.

So, the options are:

1) try mounting the db. it will soon be apparent if this approach will work. if the db comes from a commercial app, ask the vendor if it is possible to use SQL2K.

2) mount the db locally on SQL Express, export the tables using bcp to text files, then import the tables on the server using bcp. you will need to create the tables first.

3) mount the db locally on SQL Express, use data transformation services or replication to move the data over on a one shot basis.

The options are listed in my order of preference if it were me

.

[AllenB]

Hello,

I do not believe that number 1, above, will work unless you have created the dB with the compatibility level set to SQL 2000. I have not tried this yet, but have found this on the MS Forums, which may be of help:

"I have done the process of Converting SQL 2005 Database into SQL 2000 Database.

1.Create Database in SQL Server 2000 which is the name into SQL 2005.
2.From SQL 2005 Mgmt Console Studio , from Database - tasks - Generate Script
-then select Database -Script for Server Version - Change it from SQL Server 2005 to SQL Server 2000
3. Do the process as per wizard.
4.We can run that Script into SQL Query Analyzer 2000 for that selected database where you want to move it.

Its working fine.I got whole database table creation and whole relationship which was made into SQL Server 2005.

Gr8t.. thanks for your help."

HTH and best wishes.

[ThunderCloud]

Thank you for both of your help.

I have actually figured out a way by last night through some lucky googling.

I've using Microsoft Database Publishing Wizard (apparently it's a relatively new tool) which generates scripts for both data and schema and successfully installed the DB to my host's server.

I have not tried if it works properly yet because there's some user permission problems that I'll have to try to solve later, but at least the database is up!

I've tried using management studio express's tasks -> generate scripts but I can never get it to include include table data.

[cartika-andrew]

Originally Posted by ThunderCloud

I recently signed up with cartikahosting.com only to find out they do not support SQL server 2005 when I tried to install my application.

Their features and price are excellent so I would like to stay with cartika if possible, is there a way to convert my database to 2000? Or backup the database using sql server scripts?

Hello Thundercloud, great to have you aboard !!

actually, if you submit a ticket giving us remote access to your source 2005 server, we can do the export, convert to 2000 and import for you without issue.

A note on ms sql 2005 - we looked at upgrading this last month and frankly, too many client sites would break (and yes, I know the upgrade is supposed to be 100% backwards compatible, but, its not - I assure you) - so, what we have decided is to leave the current mssql server as 2000 and then add a new mssql server which will be 2005 - that way clients can choose which one they prefer. having said this, we are a little ways off from doing this - so, best to submit a ticket and our guys will happily do this conversion for you.