SQL Server 2008 conversion to SQL 2005 database script generation

This article discusses how to reduce the database from SQL Server 2008 to 2005, because local development is done in the SQL Server Express Edition version, and the host provider now provides the MSSQL only to 2005, So we need to get the database version down. This process is cumbersome, first of all you need to have SQL Server Management Studio 2008 (SSMS), and now download it first.

SQL Server Management Studio Express Download (if you are using an XP operating system, download that x86 version, that is, the 32-bit version, if it is a Windows 7 and 64-bit operating system, download that x64 version), select the " New installation, during installation, the computer system environment is subject to rigorous checks to ensure that the database is installed successfully. If you encounter a failed component (that is, a component with a Red Cross), do not close the installation window, go to the Internet to find the relevant components (preferably Microsoft official download site) installation, install one, retry once (there is a "rerun" button at the top of the installation window) until all the failed components are installed (skip and warn about it), SQL Server 2008 and the Management platform (formerly Enterprise Manager) have been installed successfully. Note During the installation process, VS2008 may need to install SP1 (note: not a. NET 3.5 SP1), this installation is quite slow, be patient.

Tip: SQL Server has a built-in version of Microsoft, and if you see a database version of 10.0 in SSMS or VS2008, SQL Server 2008, if the version is 9.0, is SQL Server 2005.

Developed for the Henan Pesticide Information Network website, has been developed on the SQL 2008, but the virtual hosting provider's SQL database currently only provides support for SQL2005, so to the already developed database (contained in the data) reduced to SQL2005 can be identified, or downgrade to SQL2005. Here's how (SSMS2008 has been downloaded and installed):

1. Click "Start > Programs > Microsoft SQL Server > SQL Server Management Studio"

2, in the "Connect to Database Server" dialog box, the server type selected "Database Engine", the server name select the local SQL Server (typically the hostname/sql server), click the drop-down box can be found, authentication selected "Windows Authentication" (General local SQL Server is this authentication, if "SQL Server Authentication" is selected, the user name and password are required, the database on the Internet is this authentication, the host service provider provides the user name and password)

3, the SSMs interface out, in the Object Explorer in the "database" directory to find the database you want to convert, if not, right-click on the "database" directory, in the shortcut menu to click on "Attach ...", a New dialog window, click the "Add ..." button in the right pane, Find the database you want to convert and click "OK"

4, right click on the database to be converted, click "Task > Generate Script" in the shortcut menu, click "Next" in the Script wizard, select the database you want to convert (if it is checked, this is checked), tick the lower "script all objects of the selected database", as

Server Convert to SQL 2005 database script generates "alt=" SQL Server /c7>2008 conversion to SQL 2005 database script generates "src=" http://static.oschina.net/ Uploads/img/201411/12143253_fzti.jpg ">

5, click Next, now enter the most critical phase of the configuration:

Since the resulting script is finally generating tables in a database that has been built by the host provider, be careful to follow the following configuration:

Write the script to create the database: False (the host provider already has a database, only the tables are generated, the database is no longer generated)

Script to write data: True (because useful data is generated together)

Script for writing use database: False

Scripting Server version: SQL Server 2005 (generate 2005 scripts instead of the default 2008 script, see)

Server convert to SQL 2005 database script generates "alt=" SQL Server convert to SQL 2005 database script generates "src=" http ://static.oschina.net/uploads/img/201411/12143253_w2xy.jpg ">

6, according to the image below the option settings, click "Done", you can complete the database from SQL Server 2008 to SQL Server 2005 conversion.

Server Convert to SQL 2005 database script generates "alt=" SQL Server /c7>2008 conversion to SQL 2005 database script generates "src=" http://static.oschina.net/ Uploads/img/201411/12143254_bdmo.jpg ">

Post-note: Then import the script directly to the network database based on the database provided by the host provider, or use this ssms to connect to the host provider's database (which is definitely SQL Server authentication, need to provide a username and password), "new query" from the network database method to paste the script just generated into the New query window, click "Run", you can import all the data tables, stored procedures, constraints, etc. into the database of the host provider, more specific issues, to contact the host provider.

In addition, if the database is similar to a backup generation script, and then the complete generation of the database, then the 5th step should do this:

Write script to create database: True (database generated together)

Script to write Data: True (contains useful data)

Script for writing use database: True

/////////////////////////////////////////////////////////////////////////////////////////////////////////////// /////////////////////////////////////////////

When you export a database with SQL2008, the database constraints such as primary key, index, and identity are lost in the database. Here, take SQL2008 as an example, how to guide the data, but also maintain the primary key, index, identity and other database constraints are not lost methods.

First, right-task (T)-Generate script (E) on the database above the local sql2008, click Next, tick "script all objects in the selected database"-Click Next-you can see "script to write Data" (SQL2005 without this option) is false, Double-click to True, pull down-you can see "Scripting for Server version", double-click to select SQL2005 (depending on the remote database version, I want to guide the database is SQL2005 so select it)-click Next-click Next-click "Finish", the script appears in Query Analyzer. Next, change the script to fit the remote database. Generally to change two places, the first place is the first row use [database] This depends on the remote database, my remote database is Sq_congxun, so the first line is changed to use [Sq_congxun]. The second place is to change the database schema, the general Local is the DBO, and the remote database is the database user as the schema, so press ctrl+h key, pop up the replacement window, the dbo to replace the Sq_congxun, this script is completed. Next open the remote database, create a new query, just copy the script in, execute, OK, congratulations, done!

/////////////////////////////////////////////////////////////////////////////////////////////////////////////// ////////////////////////////////////////////////

SQL2005 SQL2008 Remote Connection Configuration method collection
SQL2005:

Open SQL2005 Configuration Tool-->sql Server 2005 Surface Area Configuration--service and connectivity surface Area Configurator-->database---Remote connection, Select local and remote connections and use both TCP/IP and named pipes at the same time.

(Attached: How to configure SQL Server 2005 to allow remote connections: HTTP://SUPPORT.MICROSOFT.COM/KB/914277/ZH-CN)

SQL2008:

Open SQL Server Management studio--> Select the first item in the left [Object Explorer] (primary database Engine)--right----and in the Aspect drop-down list, select the [Surface area configurator]--> Set Remotedacenable to True.

Express:

If XP has an open firewall, in the exception of the following two programs to add:
C:\Program Files\Microsoft SQL Server\mssql.1\mssql\binn\sqlservr.exe,
C:\Program Files\Microsoft SQL Server\90\shared\sqlbrowser.exe

SQL Server 2008 conversion to SQL 2005 database script generation