Importing SQL2005 data into SQL2000

We have a SQL 2005 server. After a few months in production, we have
realized that our application which uses sql has a know bug in regards with
sql 2005 and we need to run sql 2000 instead until the bug is resolved. How
can we move our existing data from sql 2005 to sql2000.
Thanks.
news.microsoft.com wrote:
> We have a SQL 2005 server. After a few months in production, we have
> realized that our application which uses sql has a know bug in regards with
> sql 2005 and we need to run sql 2000 instead until the bug is resolved. How
> can we move our existing data from sql 2005 to sql2000.
> Thanks.
BCP, Integration Services or a Linked Server are three possibilities.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||If there is no SQL 2005 specific changes on the table definitions and the
code (stored procedures/functions, or XML data types), you may have to script
all the objects (tables, views, stores procedures, etc.) and execute them in
SQL Server 2000. Then copy the data using SSIS or using BCP depending on how
big is the data to move.
Even is everything goes great, test it, test it again and then test more
before considering it your new production environment.
"news.microsoft.com" wrote:

> We have a SQL 2005 server. After a few months in production, we have
> realized that our application which uses sql has a know bug in regards with
> sql 2005 and we need to run sql 2000 instead until the bug is resolved. How
> can we move our existing data from sql 2005 to sql2000.
> Thanks.
>
>

Importing SQL2005 data into SQL2000

We have a SQL 2005 server. After a few months in production, we have
realized that our application which uses sql has a know bug in regards with
sql 2005 and we need to run sql 2000 instead until the bug is resolved. How
can we move our existing data from sql 2005 to sql2000.
Thanks.news.microsoft.com wrote:
> We have a SQL 2005 server. After a few months in production, we have
> realized that our application which uses sql has a know bug in regards with
> sql 2005 and we need to run sql 2000 instead until the bug is resolved. How
> can we move our existing data from sql 2005 to sql2000.
> Thanks.
BCP, Integration Services or a Linked Server are three possibilities.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||If there is no SQL 2005 specific changes on the table definitions and the
code (stored procedures/functions, or XML data types), you may have to script
all the objects (tables, views, stores procedures, etc.) and execute them in
SQL Server 2000. Then copy the data using SSIS or using BCP depending on how
big is the data to move.
Even is everything goes great, test it, test it again and then test more
before considering it your new production environment.
"news.microsoft.com" wrote:
> We have a SQL 2005 server. After a few months in production, we have
> realized that our application which uses sql has a know bug in regards with
> sql 2005 and we need to run sql 2000 instead until the bug is resolved. How
> can we move our existing data from sql 2005 to sql2000.
> Thanks.
>
>sql

Importing SQL2005 data into SQL2000

We have a SQL 2005 server. After a few months in production, we have
realized that our application which uses sql has a know bug in regards with
sql 2005 and we need to run sql 2000 instead until the bug is resolved. How
can we move our existing data from sql 2005 to sql2000.
Thanks.news.microsoft.com wrote:
> We have a SQL 2005 server. After a few months in production, we have
> realized that our application which uses sql has a know bug in regards wit
h
> sql 2005 and we need to run sql 2000 instead until the bug is resolved. H
ow
> can we move our existing data from sql 2005 to sql2000.
> Thanks.
BCP, Integration Services or a Linked Server are three possibilities.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||If there is no SQL 2005 specific changes on the table definitions and the
code (stored procedures/functions, or XML data types), you may have to scrip
t
all the objects (tables, views, stores procedures, etc.) and execute them in
SQL Server 2000. Then copy the data using SSIS or using BCP depending on how
big is the data to move.
Even is everything goes great, test it, test it again and then test more
before considering it your new production environment.
"news.microsoft.com" wrote:

> We have a SQL 2005 server. After a few months in production, we have
> realized that our application which uses sql has a know bug in regards wit
h
> sql 2005 and we need to run sql 2000 instead until the bug is resolved. H
ow
> can we move our existing data from sql 2005 to sql2000.
> Thanks.
>
>

importing MDF database

As a newbie, I'm having problems importing a copy of my SQLServer2k development database from my laptop (via a CD), to my production SQL Server2k database.

I've tried the DTS Import/Export wizard & also the Copy wizard, but can't seem to see the dialog that prompts & identifies the source path & filename.

I've tried setting up an ODBC DSN for the database, but get the same problem.

Coming from an Access background, I've been used to the copying of *.mdb files, and half expected that level of simplicity with SQLS2K - but clearly, I'm wrong and am missing something, which the on-line help isn't able to clarify for me...

Can anyone please give me any pointers for me to get a copy of my development database along with its stored procedures, from my laptop (not networked), to my desktop server. Both use SQLS2k.

Thanks in advance, AlexSo let me get this strait. You want to restore a copy of your database thats are on a CD?|||The easiest for you would be to either copy the MDF file along with LDF file onto a local drive of the server from the CD and use Enterprise Manager (EM) to Attach Database, or create a backup on your laptop and then restore from that backup file.|||Originally posted by rdjabarov
The easiest for you would be to either copy the MDF file along with LDF file onto a local drive of the server from the CD and use Enterprise Manager (EM) to Attach Database, or create a backup on your laptop and then restore from that backup file. ...and remember to mark the files Read/Write again! I've never been bitten by that, but I read about this guy that was once!

-PatP|||This link http://vyaskn.tripod.com/moving_sql_server.htm refers moving database from one system to another, just follow instead moving consider copying the files.

Also refer to the corresponding KBAs which refers to resolve any login issue arises.|||Thanks all very much for your prompt replies. Just as I was applying the suggested "Attach" method, the motherboard in my Dell server went kaput! So I won't be able to try it until the weekend - when I hope to report back success.

Interestingly the link provided in one of the responses, was very useful and included code to automate the attachment within a stored procedure (if necessary). I aim to try the menu-dropdown "manual" attachment first, & see how I get on with that.

All the best - & thanks again
Alex|||Thanks to all who replied & helped (especially Pat, Rdjabarov, & Satya).

I've just had the server's power supply replaced & tried your suggestions - the attach worked fine (from the Enterprise>Actions>AllTasks>Attach)

Best regards, & thanks again
Alexsql

Importing into Test Database

SQL 2000, Windows 2000. I need to find a way to export our data from
our production database and import it into a test database. I can do
it to a freshly created test database I just have trouble doing it
once the test database is already populated. Is there a script I can
write that will automate it by
1) Deleting the test database
2) Creating a new one
3) Exporting the production tables and views
4) Importing into the new test database

What are my options here?

Thank you in advance."Justin Allen" <contactjustinallen@.hotmail.com> wrote in message
news:3b993d0.0402130629.581cecb4@.posting.google.co m...
> SQL 2000, Windows 2000. I need to find a way to export our data from
> our production database and import it into a test database. I can do
> it to a freshly created test database I just have trouble doing it
> once the test database is already populated. Is there a script I can
> write that will automate it by
> 1) Deleting the test database
> 2) Creating a new one
> 3) Exporting the production tables and views
> 4) Importing into the new test database
> What are my options here?
> Thank you in advance.

See my reply to your previous post.

Simon|||contactjustinallen@.hotmail.com (Justin Allen) wrote in message news:<3b993d0.0402130629.581cecb4@.posting.google.com>...
> SQL 2000, Windows 2000. I need to find a way to export our data from
> our production database and import it into a test database. I can do
> it to a freshly created test database I just have trouble doing it
> once the test database is already populated. Is there a script I can
> write that will automate it by
> 1) Deleting the test database
> 2) Creating a new one
> 3) Exporting the production tables and views
> 4) Importing into the new test database
> What are my options here?
> Thank you in advance.

If you are looking to simply bring all the data over from the source
to the destination db, you may try to dump the tables to a sql file
and run the load on the destination db.

Also have a look at sqlporter database migration tool
from realsoftstudio.com

Hope it helps.

Importing Data from a SQL 2000 Database

Hey there, this is probably me being very dense but i'll let you all tell me that anyway.

I have a production database which is SQL Server 2000 and I have a SQL Server 2005 database which is a copy of this database taken about a month ago.

I would like to set up a scheduled job to run every morning that will update the data (and only the data) in the 2005 database so it matches the production data.

I've been trying to create a package using the Import Wizard but the problem I'm having is that I would like to use the "Delete Rows in Destination Table" option which is set by clicking the edit button in the mappings column of each table. Problem is I have over 1000 tables and i'm not too keen on going through them all.

Is there any easy way to do what I want, bearing in mind that I can't alter anything on the production database I can only copy the data from it.

ThanksSo you want to delete all rows in a number of tables, correct?

Why not just write a T-SQL batch that uses a cursor to loop over all your tables, builds a dynamic SQL statement which is "TRUNCATE TABLE " + @.table_name, then executes that dynamic SQL.

If you want to run this from SSIS you can put it into an Execute SQL Task.

If you want to use SSIS to do the looping then that is easily possible using a Foreach loop. Let me know if you want to know more.

-Jamie|||

There are several programatic ways to do this but a more "straightforward" way would be to save the package. Add a SQL Task with a precedence constraint to run before the dataflow task that deletes all the data in all the tables. You can use SSMS to script the deletes for all the tables, although you have to do it in a round about manner. Select all the tables and then script. You will only be allowed to script create or drop. Script the drop and then remove all the script except for the drops commands themselves (i.e. there will be constraint altering, etc depending on how the tables where created). Then use Search and Replace to change the DROP TABLE to DELETE FROM.

HTH,
Matt