如何:生成和部署到本地数据库How to: Build and Deploy to a Local Database

Microsoft SQL Server 2012 提供称作 SQL Server Express 本地数据库运行时的本地按需运行的服务器实例,在调试 SQL Server 数据库项目时该实例将被激活。Microsoft SQL Server 2012 provides a local on-demand server instance, called SQL Server Express Local Database Runtime, which is activated when you debug a SQL Server Database project. 此本地服务器实例可以用作生成、测试和调试您的项目的沙盒。This local server instance can be used as a sandbox for building, testing and debugging your project. 它独立于已安装的任何 SQL Server 实例,并且不可从 SQL Server Data Tools (SSDT) 之外访问。It is independent of any of your installed SQL Server instances, and is not accessible outside SQL Server Data Tools (SSDT). 如果开发人员对生产数据库只有有限访问权限或没有访问权限,但想要在授权人士将其项目部署到生产前在本地测试其项目,则上述安排适合于此类开发人员。Such an arrangement is ideal for developers who have limited or no access to production databases, but would like to test their projects locally before authorized personnel will deploy them to production. 此外,在您为 SQL Azure 开发数据库解决方案时,可以利用此本地服务器所提供的便利,在将数据库项目部署到云中之前在本地开发和测试您的数据库项目。In addition, when you are developing a database solution for SQL Azure, you can utilize the convenience provided by this local server to develop and test your database project locally, before deploying it to the cloud.

警告

在 SQL Server 对象资源管理器中的本地数据库节点下的数据库表示其相应的数据库项目,与连接的服务器实例中的同名数据库无关。A database under the local database node in SQL Server Object Explorer is a reflection of its corresponding database project, and is not related to the same-named database in a connected server instance.

警告

以下过程利用在连接的数据库开发面向项目的脱机数据库开发部分中的之前过程中创建的实体。The following procedures utilize entities created in previous procedures in the Connected Database Development and Project-Oriented Offline Database Development sections.

使用本地数据库To use the local database

  1. 请注意,在“SQL Server 对象资源管理器” 中的 SQL Server 节点下,将出现一个名为“本地” 的新节点。Notice that in SQL Server Object Explorer, under the SQL Server node, a new node named Local appears. 这是本地数据库实例。This is the local database instance.

  2. 展开“本地” 和“数据库” 节点。Expand the Local and Databases nodes. 请注意具有与 TradeDev 项目同名的数据库的外观。Notice the appearance of a database with the same name as the TradeDev project. 展开此数据库下的节点。Expand the nodes under this database. “数据工具操作” 窗口可在“本地” 节点数据库上显示正在进行的扩展/导入操作的状态。The Data Tools Operations window shows the status of expansion/import operations in progress on any database in the Local node. 请注意,这些节点不包含我们在以前的过程中创建的任何表和实体。Notice that they do not contain any of the tables and entities we created in earlier procedures.

  3. 按 F5 调试 TradeDev 数据库项目。Press F5 to debug the TradeDev database project.

    默认情况下,SSDT 将使用本地数据库服务器实例来调试数据库项目。By default, SSDT will use the local database server instance for debugging database projects. 在这种情况下,SSDT 将首先尝试生成该项目,如果没有错误,该项目(及其实体)将部署到本地数据库中。In this case, SSDT will first attempt to build the project, and if there is no error, the project (and its entities) will be deployed to the local database. 如果您在以后调试相同的项目,SSDT 将检测到您在上次调试会话后进行的任何更改,并且仅将这些更改部署到本地数据库。If you debug the same project later, SSDT will detect any changes since your last debugging session, and deploy only those to the local database.

  4. 再次展开“本地” 数据库服务器中 TradeDev 下的节点。Expand the nodes under TradeDev in the Local database server again. 这一次,注意到表、视图和函数已部署到该本地数据库服务器。This time, notice that the tables, views and functions have been deployed to the local database server.

  5. 右键单击 TradeDev 节点并选择“新建查询” 。Right-click the TradeDev node and select New Query.

  6. 在脚本窗格中,粘贴以下代码并单击“执行查询” 按钮以便运行该查询。In the script pane, paste this code and click the Execute Query button to run the query.

    select * from dbo.GetProductsBySupplier(1)  
    
  7. “消息” 窗格将显示“(0 行受影响)”并且“结果” 窗格将不返回任何行。The Message pane shows (0 row(s) affected), and the Results pane returns no row. 这是因为我们对本地数据库进行查询,而非对包含实际数据的连接的数据库进行查询。This is because we are querying against the local database instead of the connected database that actually contains real data.

    可以通过右键单击这个本地 TradeDev 数据库下方的 Products 表,然后选择“查看数据” ,对此进行确认。You can confirm this by right-clicking the Products table under this local TradeDev database, and select View Data. 请注意,该表是空的。Notice that the table is empty.

将实际数据复制到本地数据库To replicate real data to the local database

  1. 在“SQL Server 对象资源管理器” 中,展开连接的 SQL Server 实例并找到 TradeDev 数据库。In SQL Server Object Explorer, expand your connected SQL Server instance and locate the TradeDev database.

    右键单击 Suppliers 表并选择“查看数据” 。Right-click Suppliers table and select View Data.

  2. 单击数据编辑器顶部的“脚本” 按钮(从右边数第二个按钮)。Click the Script button (the second button from the right) on top of the Data Editor. 从该脚本中复制 INSERT 语句。Copy the INSERT statements from the script.

  3. 展开“本地” 服务器实例并右键单击 TradeDev 节点,然后选择“新建查询” 。Expand the Local server instance and right-click the TradeDev node, select New Query.

  4. INSERT 语句粘贴到此查询窗口中并执行查询。Paste the INSERT statements into this query window and execute the query.

  5. 重复上述步骤,将数据从连接的 TradeDev 数据库的 Products 和 Fruits 表复制到本地 TradeDev 数据库。Repeat the above steps to replicate data from the Products and Fruits tables in the connected TradeDev database to the local TradeDev database.

  6. 右键单击该“本地” 服务器实例,然后选择“刷新” 。Right-click the Local server instance and select Refresh. 使用“查看数据” 对这些表进行检查,以便确认已填充该本地数据库。Examine the tables using View Data to verify that the local database has been populated.

  7. 右键单击本地服务器实例的 TradeDev 节点,然后选择“新建查询” 。Right-click the TradeDev node of the Local server instance, and select New Query.

  8. 在脚本窗格中,粘贴以下代码并单击“执行查询” 按钮以便运行该查询。In the script pane, paste this code and click the Execute Query button to run the query.

    select * from dbo.GetProductsBySupplier(1)  
    
  9. 在 Transact-SQL 编辑器窗格下的“结果” 窗格中,将看到返回 Products 表的 Apples 和 Potato Chips 行。In the Results pane below the Transact-SQL Editor pane, you will see that the Apples and Potato Chips rows of the Products table are returned.