Migrate subset of data from 2000 to 2005

First a little background.

Production server is SQL Server 2000. Various clients will be installing a desktop version of our web application. The desktop app will be running SQL Server 2005 Express (MSDE replacement, correct?). When the client downloads the package, the installer will import their data from the SQL Server 2000 production database into the SQL Server 2005 database. The production database contains numerous clients, so it doesn't make sense to migrate ALL of the data, only the data that belongs to the client.

My question is what method(s) would allow this to be done? The databases will NOT be connected to one another so using DTS packages is not an option.

After doing some research I came to the conclusion that the only way I could see this working, would be to output each tables data (just the data that belongs to the particular client) into its own text file and then import them individually on the SQL Server 2005 side.

Does anyone have any other ideas?

Kevin - I have the same dilemma currently and wondered if you had found a solution to this?

Migrate SQL 2000 to New SQL 2005 Installation.

Hi all, in my current environment, I have a single server SQL 2000 setup that's being replaced. I'm in the process of installing a new SQL 2005 cluster with the thought of taking advantage of 2005 mirroring and clustering, but have a few questions.

1. My thinking is that I can migrate my 2000 databases to 2005, but leave the databases in 8.0 (2000) mode. Are there any issues with this? I know they won't take advantage of the 2005 performance boost

2. Some of the apps don't support 2005 yet so I need to leave them in 2000 mode until they do. Is SQL 2005 fully backwards compatible with a SQL 2000 database?

3. Will mirroring work on SQL 2005 with a database that is still in 2000 mode?

Thanks for any assistance with this!

Start by running the SQL Server 2005 upgrade advisor against your SQL 2000 installation. This will give you good information on problems that you might need to address. Also, if your applications are purchased, check with the vendors for advice on compatibility. But the bottom line is that every application is unique and the only way you'll know for sure is by carefully testing.

Sorry, I don't know the answer to your question on mirroring.

Paul

|||Thanks for the response Paul, Doesn't the upgrade advisor only tell me info in regards to actually upgrading the database? I'm looking at keeping them at 8.0 compatibility on SQL 2005. Basically, I'm looking at keeping the databases at the SQL2000 level on a SQL2005 box. I guess my real question is: Are databases that are running in SQL2000 compatibility mode on a SQL2005 box fully backwards compatible?|||

Most applications will upgrade with no problems but there are a small number of breaking changes in 9.0, even if you stay in 8.0 compat mode. Look up "compatibility" and "sp_dbcmptlevel" in BOL for details.

|||

hi Tynman

Yes u can always keep SQL2000 database compatibility level i.e. 80 in sql 2005 by specifying compatibility level in system stored procedure "sp_dbcmptlevel" and give compatibility level as 80 with database name.

Ur command will be: sp_dbcmplevel dbname, 80

|||

BUMP!

3. Will mirroring work on SQL 2005 with a database that is still in 2000 mode (compatibility level 80)?

Do anyone have an answer to this question?

|||I am trying to restore a SQL 2005 DB into SQL 2000 and it errors out "too many objects on 64 allowed......blah..blah..blah.." something to that effect.

Migrate SQL 2000 to New SQL 2005 Installation.

Hi all, in my current environment, I have a single server SQL 2000 setup that's being replaced. I'm in the process of installing a new SQL 2005 cluster with the thought of taking advantage of 2005 mirroring and clustering, but have a few questions.

1. My thinking is that I can migrate my 2000 databases to 2005, but leave the databases in 8.0 (2000) mode. Are there any issues with this? I know they won't take advantage of the 2005 performance boost

2. Some of the apps don't support 2005 yet so I need to leave them in 2000 mode until they do. Is SQL 2005 fully backwards compatible with a SQL 2000 database?

3. Will mirroring work on SQL 2005 with a database that is still in 2000 mode?

Thanks for any assistance with this!

Start by running the SQL Server 2005 upgrade advisor against your SQL 2000 installation. This will give you good information on problems that you might need to address. Also, if your applications are purchased, check with the vendors for advice on compatibility. But the bottom line is that every application is unique and the only way you'll know for sure is by carefully testing.

Sorry, I don't know the answer to your question on mirroring.

Paul

|||Thanks for the response Paul, Doesn't the upgrade advisor only tell me info in regards to actually upgrading the database? I'm looking at keeping them at 8.0 compatibility on SQL 2005. Basically, I'm looking at keeping the databases at the SQL2000 level on a SQL2005 box. I guess my real question is: Are databases that are running in SQL2000 compatibility mode on a SQL2005 box fully backwards compatible?|||

Most applications will upgrade with no problems but there are a small number of breaking changes in 9.0, even if you stay in 8.0 compat mode. Look up "compatibility" and "sp_dbcmptlevel" in BOL for details.

|||

hi Tynman

Yes u can always keep SQL2000 database compatibility level i.e. 80 in sql 2005 by specifying compatibility level in system stored procedure "sp_dbcmptlevel" and give compatibility level as 80 with database name.

Ur command will be: sp_dbcmplevel dbname, 80

|||

BUMP!

3. Will mirroring work on SQL 2005 with a database that is still in 2000 mode (compatibility level 80)?

Do anyone have an answer to this question?

|||I am trying to restore a SQL 2005 DB into SQL 2000 and it errors out "too many objects on 64 allowed......blah..blah..blah.." something to that effect.sql

Migrate SQL 2000 to New SQL 2005 Installation.

Hi all, in my current environment, I have a single server SQL 2000 setup that's being replaced. I'm in the process of installing a new SQL 2005 cluster with the thought of taking advantage of 2005 mirroring and clustering, but have a few questions.

1. My thinking is that I can migrate my 2000 databases to 2005, but leave the databases in 8.0 (2000) mode. Are there any issues with this? I know they won't take advantage of the 2005 performance boost

2. Some of the apps don't support 2005 yet so I need to leave them in 2000 mode until they do. Is SQL 2005 fully backwards compatible with a SQL 2000 database?

3. Will mirroring work on SQL 2005 with a database that is still in 2000 mode?

Thanks for any assistance with this!

Start by running the SQL Server 2005 upgrade advisor against your SQL 2000 installation. This will give you good information on problems that you might need to address. Also, if your applications are purchased, check with the vendors for advice on compatibility. But the bottom line is that every application is unique and the only way you'll know for sure is by carefully testing.

Sorry, I don't know the answer to your question on mirroring.

Paul

|||Thanks for the response Paul, Doesn't the upgrade advisor only tell me info in regards to actually upgrading the database? I'm looking at keeping them at 8.0 compatibility on SQL 2005. Basically, I'm looking at keeping the databases at the SQL2000 level on a SQL2005 box. I guess my real question is: Are databases that are running in SQL2000 compatibility mode on a SQL2005 box fully backwards compatible?|||

Most applications will upgrade with no problems but there are a small number of breaking changes in 9.0, even if you stay in 8.0 compat mode. Look up "compatibility" and "sp_dbcmptlevel" in BOL for details.

|||

hi Tynman

Yes u can always keep SQL2000 database compatibility level i.e. 80 in sql 2005 by specifying compatibility level in system stored procedure "sp_dbcmptlevel" and give compatibility level as 80 with database name.

Ur command will be: sp_dbcmplevel dbname, 80

|||

BUMP!

3. Will mirroring work on SQL 2005 with a database that is still in 2000 mode (compatibility level 80)?

Do anyone have an answer to this question?

|||I am trying to restore a SQL 2005 DB into SQL 2000 and it errors out "too many objects on 64 allowed......blah..blah..blah.." something to that effect.

Microsoft user group

Sorry for this post but can anyone direct me to Windows XP
Home edition user group ? I Think I locked myself out of
my computer unless Installing SP2 messed me up which is
not installed at all......
Thanks.....
Users group or newsgroup?
For newsgroups, try:
microsoft.public.windowsxp.help_and_support
-Sue
On Mon, 15 Nov 2004 11:20:44 -0800, "Dan"
<anonymous@.discussions.microsoft.com> wrote:

>Sorry for this post but can anyone direct me to Windows XP
>Home edition user group ? I Think I locked myself out of
>my computer unless Installing SP2 messed me up which is
>not installed at all......
>Thanks.....

Microsoft user group

Sorry for this post but can anyone direct me to Windows XP
Home edition user group ? I Think I locked myself out of
my computer unless Installing SP2 messed me up which is
not installed at all......
Thanks.....
"Dan" <anonymous@.discussions.microsoft.com> wrote in news:5be201c4cb48
$325c2540$a401280a@.phx.gbl:

> Sorry for this post but can anyone direct me to Windows XP
> Home edition user group ? I Think I locked myself out of
> my computer unless Installing SP2 messed me up which is
> not installed at all......
> Thanks.....
>
alt.os.windows-xp

Microsoft user group

Sorry for this post but can anyone direct me to Windows XP
Home edition user group ' I Think I locked myself out of
my computer unless Installing SP2 messed me up which is
not installed at all......
Thanks....."Dan" <anonymous@.discussions.microsoft.com> wrote in news:5be201c4cb48
$325c2540$a401280a@.phx.gbl:

> Sorry for this post but can anyone direct me to Windows XP
> Home edition user group ' I Think I locked myself out of
> my computer unless Installing SP2 messed me up which is
> not installed at all......
> Thanks.....
>
alt.os.windows-xp