用DPW(Database Publishing Wizard)进行SQL Server数据库发布

很多数据库都提供提供数据库定义和数据导入功能，但不幸的是SQL Server却没有，导出成SQL语句只会到处数据库对象的定义，并不包含数据，为了达到导出数据的功能，我们通常只能通过数据库附加或者备份的功能实现，而无法像MySQL那样利用一个SQL语句就能完成数据库的定义和数据导入。

好了，SQL Server现在也提供了这种功能了，它用到了一个软件数据库发布向导(Database Publishing Wizard)，它可以免费获取，该向导支持2种数据库主机部署场景：

1) 数据库发布向导允许你指向一个你正在本机上操作的数据库，然后自动生成一个.SQL脚本文件，该文件包含了在任何远程系统上重建一个当前数据库的完整拷贝所需的安装逻辑。这个.SQL脚本包括了需要创建数据库定义(表，视图，存储过程，触发器，全文索引目录，角色，规则等等，可在此处参考所有细节)，以及把与你本地数据库同样的数据内容填充到新数据库里去的所有的东西(这类似于MySQL 的dump工具)。把这些安装逻辑封装在单个.SQL 文件的好处是，大多数主机供应商已经支持上传 .SQL 文件到他们的主机环境，并且通过他们的管理控制面板运行这些脚本的能力。假定你现有一个支持这个功能的web主机供应商，你可以马上开始使用数据库发布向导来轻松地部署你的网站，而不需要主机供应商安装或配置什么东西。

2) 数据库发布向导也允许你指向一个你正在本机上操作的数据库，然后使用 web service 向你的远程主机环境迁移和重建数据库(而不用创建一个.SQL 文件或者使用主机供应商的管理控制面板运行这个文件)。不过，这个发布选项需要主机环境提供SQL发布web-service。 SQL Server Hosting Toolkit包含了一个免费的SQL发布web-service实现，我们将与主机供应商密切合作来部署这个web-service。

数据库发布向导允许你在本地使用 SQL Express 或 SQL Server 2000/2005 ，然后在远程主机环境中使用SQL 2000 或 SQL 2005。它不要求SQL服务器的版本是一样的，所以，你可以在本地使用SQL Express 2005，然后上传到主机环境中的SQL 2000服务器，而不用改动任何编码。

数据库发布向导也支持对内置的ASP.NET 2.0成员，角色管理，用户信息和健康监测等数据定义的处理。许多人遇上问题，因为随 ASP.NET一起发布的来建立这些数据定义的内置.SQL 脚本在安装时(install-time)需要DBO权限，但很多主机供应商对此并不支持(注：脚本在运行时(runtime)不需要DBO权限，只是在安装时(install time)才需要，但这有时会是个障碍，除非主机供应商愿意为你安装)。而在另一方面，数据库发布向导在安装ASP.NET 2.0成员，角色管理，用户信息等数据定义和数据时，并不需要DBO权限，应该允许你，和使用数据库发布向导部署其他数据库一样，轻松地部署 ASPNETDB数据表和存储过程。

最后需要说明的，如果你不是使用VS2005、没有安装SQL Server2005你可能还需要安装其它组件：

Microsoft SQL Server Native Client  
下载地址：http://download.microsoft.com/download/a/c/3/ac37a75a-1e0b-49ba-aea1-0f9b258e4965/sqlncli.msi

Microsoft SQL Server 2005 Management Objects Collection
下载地址：http://download.microsoft.com/download/f/7/4/f74cbdb1-87e2-4794-9186-e3ad6bd54b41/SQLServer2005_XMO.msi

最主要的东东，Database Publishing Wizard
下载地址：http://download.microsoft.com/download/2/1/b/21b68714-3d9a-4610-9eba-88509ffc27fb/DatabasePublishingWizard.msi

具体的安装顺序了全部软件如下：

1、Microsoft .NET Framework Version 2.0

2、Microsoft Core XML Services (MSXML) 6.0

3、Microsoft SQL Server Native Client

4、Microsoft SQL Server 2005 Management Objects Collection

5、Microsoft SQL Server Database Publishing Wizard 1.1

以上5个下载后全部安装，注意按照顺序。

以下是具体的操作方法，是英文的，就不翻译了。原文在Publishing a Database Using the Database Publishing Wizard。

This tutorial shows you how to publish a database using the Database Publishing Wizard (DPW).

To Publish Your Database Using the DPW

1. Launch the Database Publishing Wizard Client. If the welcome screen displays, check the Do not show this starting page again box.
2. Click Next.

   

3. On the Select Source Server screen, enter the connection information for your local SQL server.

   Note: This is NOT the same information as that used for your hosted server. If you are publishing from an instance of SQL server running on your desktop or local network, you use the Specify server information section. If you are publishing from a remote database, connect via a connection string. For specific connection information, contact the database server owner.

   

4. Once you have entered the connection details, click Next. This step brings up the Select Database screen, displaying a list of all databases available on your local server.

   Note: If you do not see the database you want to publish, press Back to return to the Select Source Server page and then return to this page without changing connection details. If this step does not work, contact the server owner for support.

   

5. Select the database you want to publish. If your database contains prohibited objects, such as full text catalogs and assemblies, or you do not want to publish all objects, uncheck the Script all objects in the selected database.
6. Press Next.
7. If you are not publishing all objects, the Choose Objects Type appears.

   If You Are Not Publishing All Objects

   1. Select the Object types to be published. You will be able to pick specific objects of each type on later screens.

      

   2. Click Next. This step brings up the Choose screen for each object type selected (for example, Tables and Views). If you wish to publish all objects of a given type, click Select All.

      

   3. Once you have selected all objects to be published, press Next to advance. Repeat this step until you reach the Select an Output Location screen.
8. The Select an Output Location screen appears.

   

9. Select the Publish to Shared hosting provider option. Select the appropriate Provider and Target Database from the drop down lists.
10. If you have not yet configured a connection or need to make a new connection, press More. The Hosting Providers dialog displays.

    

    If You Have Not Configured a Connection

    1. Press New. The Provider Configuration dialog displays.
    2. Enter the following values:

       Name:

       Enter any name to represent the connection to the hosted service. You will enter names for specific databases later.

       Web Service Address:

       Enter the URL for the publishing service. The URL can be found in the database information section of your hosting control panel.

       Note: The service requires a secure (HTTPS) connection to protect data. If you attempt to connect using a non-secure connection, the connection will fail and you will be prompted to use a secure connection.

       User name:

       Enter your FTP account user name.

       Password:

       Enter your FTP account password.

       Note: Checking “Save Password” causes the application to store your password locally. If this is a public or shared machine, this may not be a secure choice.

       

    3. To enter a database, press New. This displays the Database dialog.
    4. Enter the appropriate configuration values. The configuration values can be found in the database information section of your hosting control panel.

       Server Name:

       Enter the full path to the database server.

       Database Name:

       Enter the name of your hosted database.

       Note: The database must be owned by the FTP account entered in the provider configuration. If you have multiple hosting accounts, be sure to use the correct credentials when configuring the Publishing Wizard.

       User Name:

       Enter your database user name. This is the same user name used to access the SQL Web Manager.

       Password:

       Enter your database password. This is the same password used to access your SQL Web Manager.

       Note: Checking Save Password causes the application to store your password locally. If this is a public or shared machine, this may not be a secure choice.

       

    5. Click OK to complete the database set-up. Repeat this step for each database you want to configure. If you have multiple databases owned by a single hosting account, they can all be entered under a single hosting provider entry.
    6. When you have completed entering databases, click OK on the Provider Configuration dialog to return to the Hosting Providers list. You can test your connection settings by pressing Test. This feature validates the connection to the web service but does not test connectivity to the database itself.
    7. Click OK to return to the Select an Output Location dialog. If the appropriate provider and database are not selected, select them in the drop down lists.
    8. Click Next to advance to the Select Publishing Options screen. Before this page is displayed, the service validates the configuration settings by connecting to the publishing service and the target database. If a connection cannot be established, an error message displays and the application remains on the Select an Output Location screen.
11. The Select Publishing Options page is pre-populated with the appropriate default values. In most cases, these do not need to be modified.

    Drop existing objects in script:

    If True, the wizard checks if a published object exists in the database and, if found, drops the existing version before publishing.

    Note: Any data contained within dropped objects will be lost.

    If False, the wizard does not drop existing objects before inserting or modifying their structure. If an object of the type and name specified already exists, the wizard proceeds directly to data insertion. If the object does not exist, it will be created.

    Note: If the target database contains an object of the same name and type but with a different structure (for example, a table with a different number of columns), a publishing error may occur. Certain conditions may not report an error (for example, the database being published contains a table with fewer columns than the existing table). In such cases, the published database will not exactly match the local database.

    Publish using transaction:

    If True, the database is published as a single transaction. If an error occurs, all changes are rolled back.

    If False, published changes are committed as they are published. If an error occurs, the database may be left in a partially published state.

    Schema qualify:

    If True, objects are fully schema qualified in the script.

    If False, only the object name is used. It is recommended this option be True.

    Script for target database:

    Specifies the version of the target database. This must match the server version or publish will fail.  The wizard sets this value to the correct version.

    Types of data to publish:

    This section specifies what type of information to publish.

    Schema only publishes the structure of objects.

    Data only populates tables.

    Schema and data publishes the structure of objects and populates tables.

    

12. Click Next to advance. This step brings up the Review Summary page, which displays the selected options and configuration. This is the last chance to review before publishing begins.

    

13. Click Finish to begin publishing.

The Database Publishing Progress screen tracks the progress of the publish.  Based on the options selected, the wizard displays some combination of the following steps:

Gathering List of Objects to script:

The wizard is communicating with your local SQL Server and determining what objects must be scripted, including any objects you selected and their dependencies.

Connecting to web service:

The Wizard is connecting to the web service and establishing a publishing session.

Scripting objects:

The Wizard is performing preliminary scripting of the objects to be published.

Processing:

The wizard is completing local scripting of an object and transmitting it to the Web service. The Web service is executing the script against your hosted database server. The status message updates based on the specific object being published at a given instance.

The amount of time to publish a database varies based on complexity of the database, number of objects, and the amount of data in tables. Most databases complete publishing in under 30 minutes.

It is possible to abort processing at any point by pressing Stop. If an error occurs, a red link to more details is provided in the Message column. If publishing is aborted or an error occurs, you can use Back to move back through the screens and modify settings.

If publishing succeeds, press Close to close the application. Your database is now transferred to the hosting server and ready for use.