SQL Server 2005 database to SQL Server 2000 method summary _mssql

Today, a friend recommended a job is to transfer SQL Server 2005 database to SQL Server 2000, but the actual operation encountered a lot of problems, online search a number of ways to display here, and the use of my problems and solutions are recorded together for later inspection

The previous steps are the same as those on the Web, Mr. 2000 's script, and then the execution in 2000. The operation is as follows:

Open manger Studio for SQL Server 2005

--Open "Object Explorer" (no words pressed F8), connect to your instance

Find the database you want to convert (it is recommended that you empty the login in this database before the conversion will cause problems)

--Right-select the database to be converted to SQL Server2000

--Select a task

--Select Build script

--In the "Select Database" of the Script wizard, make sure that you select the library that you want to go to 2000

--Check "script all objects in the selected database" (This hook is not actually possible, depending on your needs.) ）

--In the next "Choose script Options", locate the "Script for server version" entry, select SQL Server 2000

--other options set as needed

--Finally, save the script to a. SQL script file

Copy Code code as follows:

Problems that may be encountered:
The target server version does not support this object or does not support one of its properties. (MICROSOFT.SQLSERVER.SMO)
The script could not be successfully built. This problem is caused by the database compatibility level.
Solution:
The server has a statement that changes the compatibility level:
sp_dbcmptlevel [[@dbname =] name] [, [@new_cmptlevel =] version]
Statement as follows:
EXEC sp_dbcmptlevel ' Your database name ', ' 80 ';
Go
This 80 is the version number corresponding to each version.
= SQL Server 6.0
$ = SQL Server 6.5
m = SQL Server 7.0
% = SQL Server 2000--8.0
% = SQL Server 2005--9.0
Because it's going to 2000, then the compatibility level will be 80.

2. Create the target database in 2000
In Query Analyzer (or 2005 of Manger Studio opens the script file), connect to SQL Server 2000 and execute the script generated above. To create a new database.

Copy Code code as follows:

Problems that may arise:
There's a syntax error near Max.
There is no Max in server2000, so it is recommended that you change the max in the SQL script to a very large number, such as 4000 (4000 seems to be the upper limit).
ODB problem
Other databases I don't know, but no matter what database you use server, as long as it is not your own, usually the administrator will not give you ODB permission, so all the odb wiped off on it. There is also a way, with your remote database login can be changed to the owner of that user, the local also modified, and then run, OK.

3. Lead the data from 2005 to 2000

First open manage Studio for SQL Server 2005
--Open "Object Explorer" (no words pressed F8), connect to your instance
--Right-click the database to be converted to SQL Server2000--Select task--Export data
(Note that if your manage Studio is a express version, there is no export data option in the task, you can directly find the Binn folder in your data installation directory, such as C:\Program Files\Microsoft SQL Server\90 \dts\binn then executes the DTSWizard.exe file. ）
--In the "Select data source" step of the SQL Server Import and Export Wizard, determine which database to export is selected
--In the Select target step, connect to 2000 and select the library you created in step 2
--In the Select Source table and Source view, select all tables (Note that if you have an identity in the table, select the table here, click the Edit Map button below, and check "insert with Identity" in the new open window, and each labeled table will be selected this way.) Also, here to import data if involved in the main foreign key relationship or other constraints, must be divided into batches according to the relationship of the order of import, or will be an error. ）
--finally completed

Copy Code code as follows:

Finally, note that the 2000 select top heel cannot be an expression, so if your page is useful for a similar
DECLARE @pageIndex int
Select Top (@pageIndex-1) *12 NewsID from News
, please change to a string concatenation method, such as the following example:
DECLARE @strSql varchar (1000)
DECLARE @pageIndex int
Set @pageIndex = (@pageIndex-1) *12
Set @strSql = ' Select Top ' +str (@pageIndex) + ' NewsID from News '
EXEC (@strSql)

Steps for SQL Server 2005 to 2000 (easy version, nice way to go)

2005 Go to 2000 steps
1. Generate a For 2000 version of the database script
Manger Studio, 2005
--Open Object Explorer (no words pressed F8), connect to your instance
--right key to go to 2000 of the library
-Mission
--Generating scripts
--In the Script Wizard's selection database, make sure to select the library to go to 2000
--Write scripts for all objects in the selected database
--In the next selection script option, set "script to create DATABASE" to True, find script entry for server version, select SQL Server 2000
--other options set as needed
--Finally, save the script to a. SQL script file

2. Create the target database in 2000
In Query Analyzer (or 2005 of Manger Studio opens the script file), connect to SQL Server 2000 and execute the script generated above. To create a new database

3. To turn data from 2005 to C
to open SQL Server 2000 Enterprise Manager
--right-click the database to import data
-task
-Import data
--in SQL Server In the selection data source step of the Import and Export Wizard, you decide to select the database you want to export
--in the Select data source step, connect to 2005, and select the source database
--Select all Tables
--last completed

in the Select Source table and Source view