Visual Studio2017 Database Schema comparison

Original: Visual Studio2017 database schema comparison

First, preface

Development time on the test server and the network server has our database, when we modify or add some fields on the online network, the database of the network needs to be updated, this time according to the table modification records, and then the online network above one to increase the modification is wasteful and error-prone, Is there a better way to solve the problem? Yes! That's the architecture comparison feature that comes with vs.

Individuals feel that in the early stages of development, in addition to mastering the necessary development knowledge, the use of development tools and understanding of the level is also critical. Just want to know the extent of the API, skilled use lets you develop the heart of the hand.

Ii. about

Starting with the version of Visual Studio 2005, VS has started to support the "compare and Synchronize Database Schemas", which Microsoft has done so long ago. This is a feature we will often use. This feature can be implemented by ourselves (by comparing views, stored procedures, and table structures in SQL Server), or packaged as a tool (self-developed MSSQL architecture and data comparison gadget). For beginners a little bit of trouble, here is not to say, interested can see.

Iii. start of the walkthrough

This walkthrough uses VS2017 's own SQL Server tools to compare the schemas of two databases and synchronize the schema and source schemas of the target.

1. Prepare two databases to compare

I have two databases, one local and one test server. This walkthrough will connect me to these two databases.

2. Connecting the source and destination databases

The so-called source and target databases are literally meaning (Source > target) to synchronize the new modifications in the source data to the target database.

First we open the Vs> tool >sql server> new schema comparison.

Select source

We select the database, click Select Connection

Connect to the source server and verify that you don't forget to select the database

Select target action and select source

Connect to the target server and verify that you don't forget to select the database

3. Comparing the source and target databases

When both the source and target databases are selected, click Compare in the top left corner to start comparing two databases

Here are the differences between the source and target databases

4. Update the target database

When the comparison is complete, select the update in the upper-left corner to update the field data we select to update (the field in the red tag circled in the figure)

The synchronization of data is actually the difference between generating the corresponding script and then executing it in the target database. Select View results to see the deployment script

See the tip We know the update is complete. Then we'll do a "comparison" to see the difference.

This time we will find that the synchronization has been synchronized, if you want to all sync, all tick is, architecture comparison is so simple, this walkthrough is here.

Iv. Common problems

1. Why my interface is not the same as yours
A: I vs2017 with the tool, there may be some differences than other versions, but the general operation is the same.

2. Why some of me will update the failure
A: I have also encountered this problem because it could cause the target data to be lost, or because of insufficient permissions. This I am manual operation, if you know the good solution can leave a message to me oh.

(48,1): SQL72014: .Net SqlClient Data Provider: 消息 50000，级别 16，状态 127，第 6 行 检测到行。由于可能丢失数据，正在终止架构更新。(43,0): SQL72045: 脚本执行错误。执行的脚本:IF EXISTS (SELECT TOP 1 1           FROM   [dbo].[CR_ClassRingNewsFeed])    RAISERROR (N‘检测到行。由于可能丢失数据，正在终止架构更新。‘, 16, 127)        WITH NOWAIT;执行批处理时出错。

Reference: Walkthrough: Comparing schemas for two databases

Search

Copy

Visual Studio2017 Database Schema comparison