Convert SQL server2008 database to SQL2005 database

Recently, when I was helping a Q-You website, my local backup database was SQL08, and his. NET Server is installed with SQL Server05, so we need to convert the database I backed up to 05. The process is as follows: first go to SQL Server Management Studio, log on to the Server, and then

1. If the database to be converted is in "object Resource Manager", right-click the database to be converted (dy14 database in the figure below)

2. If the database you want to convert is not in "object Resource Manager", perform the following operations:

3. In the pop-up "script wizard" window, select "write scripts for all objects in the selected Database"

4. In the pop-up "select script option" option, perform the following operations:

 

  
Write a data script: True (because useful data must be generated together)
  
Compile the Use DataBase Script: False
  
Compile the database creation script: False (the host provider already has a database. Here, only tables are generated and databases are no longer generated)
  
Write a script for the Server version: SQL Server 2005 (generate a 2005 script instead of the default 2008 script)
 

5. In "output options", perform the following operations:

6. Click "next" to complete the conversion and generate a file with the suffix ". SQL" (for example, dy14. SQL ). Then, restore the file to the new database.

Some conversion methods for online sorting

Convert SQL server2008 database to SQL2005 database

Change SQL server 2008 to SQL2005

In general, it is not necessary to convert the high version to the lower version. Today I am using the local sql2008 database and the server is using the sql2005 database, so this problem occurs, the following is an article to share with you.

2008 to 2005 steps

1. Generate database scripts for Version 2005
Manger studio of 2008
-- Open "object Resource Manager" (if not, press F8) to connect to your instance
-- Right-click the database to go to 2005
-- Task
-- Generate scripts
-- In the "select database" in the "script Wizard", make sure that the selected database is to be transferred to 2005.
-- Select "write scripts for all objects in the selected database" 5
-- In the next "select script options", set "write database creation script" to "True", find "write script for Server version", and select "SQL Server 2005"
-- Set other options as needed
-- Finally, save the script to a. SQL script file.

2. Create the target database in 2005

In the query analyzer (or 2008 manger studio opens the script file), connect to SQL Server 2005 and execute the script generated above to create a new database.

3. Import data from 2008 to 2005
Open the Enterprise Manager of SQL Server 2005
-- Right-click the database to import data
-- Task
-- Import data
-- In the "select data source" step of the "SQL Server import and export wizard", select the database to be exported.
-- In the "select data source" step, connect to 2005 and select the source database
-- In "select source table and source view", select all tables
-- Final completion

Use a Transact-SQL statement to import and export data:

1. Query access data in SQL SERVER:
-- ===================================================== ====================

The code is as follows:	Copy code
SELECT * FROM OpenDataSource ('Microsoft. Jet. OLEDB.4.0 ', 'Data Source = "c:/DB. mdb"; User ID = Admin; Password = ')... table name Select * from opendatasource ('Microsoft. jet. OLEDB.4.0 ', 'data Source = "D:/db. mdb "; User ID = Admin; Password = ')... student where name = 'me' Select * from newTable

Bytes -------------------------------------------------------------------------------------------------
2. Import access to SQL server
-- ===================================================== ====================
Run in SQL SERVER:

The code is as follows:	Copy code
SELECT * INTO newtable From opendatasource ('Microsoft. Jet. OLEDB.4.0 ', 'Data Source = "c:/DB. mdb"; User ID = Admin; Password = ')... table name

Bytes -------------------------------------------------------------------------------------------------
3. Insert data in the SQL SERVER table to the Access table
-- ===================================================== ====================
Run in SQL SERVER:

The code is as follows:	Copy code
Insert into OpenDataSource ('Microsoft. Jet. OLEDB.4.0 ', 'Data Source = "c:/DB. mdb"; User ID = Admin; Password = ')... table name (Column name 1, column name 2) Select column name 1, column name 2 from SQL table

Instance:

The code is as follows:

Copy code

Insert into OPENROWSET ('Microsoft. Jet. OLEDB.4.0 ', 'C:/db. mdb '; 'admin'; '', Test) Select id, name from Test Insert into openrowset ('Microsoft. Jet. OLEDB.4.0 ', 'C:/trade. mdb'; 'admin'; '', table name) SELECT * FROM sqltablename Insert into OpenDataSource ('Microsoft. jet. OLEDB.4.0 ', 'data Source = "D:/db. mdb "; User ID = Admin; Password = ')... student (studentid, name) select staffid, name from staff Insert into OPENROWSET ('Microsoft. Jet. OLEDB.4.0 ', 'D:/db. mdb '; 'admin'; '', Student) Select accountid, name from tblbaccount Select * from openrowset ('Microsoft. Jet. OLEDB.4.0 ', 'd:/db. mdb'; 'admin'; '', Student)

Bytes -------------------------------------------------------------------------------------------------