Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ScAndal
Starting Member
8 Posts |
 Posted - 2002-01-30 : 10:20:03
|
| We did development of a database on SQL 2000 and we need to restore it to a SQL 7 Server environment.Please let me know what would be the easiest way.Thanks,ScAndal |
|
|
andre
Constraint Violating Yak Guru
259 Posts |
Posted - 2002-01-30 : 10:29:53
|
| Here are the methods you can use from easiest to hardest:1) Use the SQL Server 2000 Copy Wizard2) If that fails, backup the database on the SQL 2000 server and restore it on 73) Generate scripts on the SQL 2000 and BCP the data into text files. Run the scripts on the SQL 7 server and BULK INSERT the data into the tables |
 |
|
|
ScAndal
Starting Member
8 Posts |
Posted - 2002-01-30 : 10:33:48
|
| Andre,Is there any way to use DTS to script data?ScAndalI tried backup/restore and it failed. |
|
|
|
andre
Constraint Violating Yak Guru
259 Posts |
Posted - 2002-01-30 : 10:36:09
|
| DTS is another option. It can copy the tables and data to the SQL 7 server. I've never used it for scripting purposes though. |
|
|
|
ScAndal
Starting Member
8 Posts |
Posted - 2002-01-30 : 10:52:01
|
| andre,Is there any way to use bcp to dump all tables, or do you have to do it table by table?Kevin |
|
|
|
andre
Constraint Violating Yak Guru
259 Posts |
Posted - 2002-01-30 : 10:56:10
|
| I don't know of any. The closest thing you can do is query one of the system tables for all the tables in your database and use dynamic SQL to BCP the data out. I don't know which table that is though. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-01-30 : 11:48:12
|
| Use DTS, that would be the simplest way. You could also use BCP. I think those are your only options.Backup/Restore won't work, detach/attach won't work, and I don't think copy DB wizard will work (Not sure about that one).I would create a DTS Package that copies all objects.HTH-Chad |
|
|
|
monkeybite
Posting Yak Master
152 Posts |
Posted - 2002-01-30 : 12:15:03
|
quote:
I would create a DTS Package that copies all objects.
I've done this before. There are features that DTS will script on object-create that aren't compatible with SQL 7.0. I ran into problems if there was cascade-update and cascade-delete enabled on table relations. If this is the case, you'll need to generate the database scripts from SQL 2000 EM, comment out the parts that aren't compatible, then run the script manually on your 7.0 box.Then you can use DTS to copy the data over. If your database is rather sophisticated, i.e. lots of parent-child relationships, you may have to transfer tables one-at-a-time. It'll take longer than you think.Finally, if you aren't running your 2000 database in 70 compatibility mode, you may want to switch it to 70, then thoroughly test your application before you deploy.Good luck.--monkey |
|
|
|
|
|
|
|
|
Copy a Sql 2000 Database to Sql 7 Server?