Data conversion methods between SQL Server 2005 and SQL 2000 _mssql2005

2005 whether it is performance or security is the increase in SQL SERVER2000, many existing enterprises in the use of 2005, or some users are 2000 and 2005 at the same time, then the middle of the problem involves dual data conversion:

Today we'll take a look at the relevant implementation methods:

My implementation of the environment introduction:

Method One: Use detach and attach this method is suitable for converting data from SQL Server 2000 to SQL Server2005, but not vice versa.

For example, the Dufei database in SQL SERVER2000 is now imported into SQL SERVER 2005 by separating and attaching the methods:

Step 1: Run on the 2000 machine:

Use master; Go
EXEC sp_detach_db @dbname = N ' Dufei '; Go

Step 2: Copy the detached MDF and LDF files to the 2005 machine:

Step 3: On the 2005 machine, execute the following command: Attach the copied file to the database:

Use master; Go
EXEC sp_attach_db @dbname = n ' Dufei ', 
@filename1 = n ' c:\dufei_data.mdf ', 
@filename2 = N ' c:\dufei_ Log.ldf '; Go

At this time: Import success!

Method Two: Use Backup and restore:

Instance: After the DB1 database in 2000 is backed up, the Restore method is used to import into 2005:

Step 1: Execute in SQL2000:

Backup database DB1 to disk= ' c:\DB1. BAK '

Step 2: Copy the backup files to the machine on SQL 2005: Copy the generated db1.bak to the other machine:

Step 3: Execute on the SQL 2005 machine:

Restore database db1 from disk= ' C:\db1.bak '

Because of the path problem, it will not succeed by default: that is, if you use the default syntax for restore:

So, we're going to modify the path:

RESTORE DATABASE db1 from DISK = ' C:\db1.bak ' with FILE = 1, move N ' db1_data ' to n ' c:\Program Files\Microsoft SQL Server\m SSQL.1\MSSQL\DB1. MDF ', move n ' db1_log ' to n ' c:\Program Files\Microsoft SQL Server\mssql.1\mssql\db1_1.ldf '

The Restore succeeds!

However, if you use this method to guide the data in 2005 to 2000, you will get an error:

Too many devices are specified for backup or restore operations; only 64 devices are allowed.

Server: Message 3013, Level 16, State 1, line 1

The RESTORE DATABASE operation terminated abnormally.

Neither method can implement SQL Server 2005 data to SQL Server 2000 unless you use the following two methods:

Method Three: Import Export:

Instance: Export database Dufei in 2005 to SQL 2000

Step 1:

Success!

Conversely, it is the same from SQL2000 to SQL 2005: for example, to guide the pubs to 2005

Method Four: Use script: But I personally do not recommend using this method, because in this process will still use the data import/export to transform the data, that is, the use of scripts can not let the records are also imported into the other's database, still need to use other methods! If you have more objects in your database, such as triggers, stored procedures, and so on, consider combining scripts with data import \ Export.

This is only my personal feeling, I do not know whether to, but also hope that everyone criticized correct!

This article comes from "Duffy" blog