How do I backup and restore a Database(SQL 6.5 -> SQL2000)? 2

[dakle]

Hi!

I am about to take a backup(full) on a SQL Server 6.5 database and I want to restore it on a SQL 2000 Server.
How do I do that? I have heard that it is not as easy as it would be if I had my database on a SQL 7.0(insted of 6.5).

If someone had a list that can walk me through the steps I would be more than happy.

Thanks, David

 

[tlbroadbent]

You cannot restore a 6.5 backup to SQL 2000. You can import from a 6.5 databases to SQL 2000 using Data Transformation Services. (DTS). Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[rajeevnandanmishra]

Hi david,
The best way to convert the database is upgrading it through Upgrade wizard.
Other wise you have to do like following.
1)Create the database on the SQL2000 server.
2)From SQL 6.5 server generate the scripts for all OBJECTS.
In SQL2K database
3)Run Script for user defined data-types, if any.
4)Run Script for Table Creation. (Just the create table statements and not the script for constraints or indexes)
5)Run Script for Constraints
6)Run Script for Indexes
7)Run Script for triggers
8)Run Script for Procedures
Now From the data transformation wizard import the data from SQL 6.5 to SQL 2K Database.

Let me know if you face any problem.

 

[dakle]

Can I convert a database by upgrading the server through the Upgrade Wizard? Is it possible when your dealing with a SQL Server 6.5.

My job is to upgrade about 40 "programs" that use SQL 6.5 and SQL 7.0 to SQL 2000. My clients want to move the programs to a test environment first where I can make sure that the programs work after the upgrade. Then I will move them back to the "business" environment.

When I am dealing with SQL 7.0 can I just take a full backup and then restore it on a SQL 2000 server?

Thanks for your awnser!

Regards David

 

[rajeevnandanmishra]

Hi David,
just have a look on:

http://msdn.microsoft.com/library/en-us/instsql/in_upgrade_5dko.asp