Skip to main content

SQL Schema Compare with Visual Studio (A complete Guide)

Introduction

When you're working on your Dev Database, an urgent issue comes along, and you instantly solve it by changing Scheme in the Staging Database or Production Database :3, few more these type of patching and you're completely out of sync!

A lot of paid alternatives are there like SQL Data Compare by RedGate, but my first choice is Visual Studio's SQL Data Tools. In the following article, I tried to image-describe the steps for SQL Data Tool.

Like I said before, there are lots of handly DBAtools out there to compare Schema between two DB Sources. I would like to discuss how you can compare two SQL Server DB with Visual Studio.

Make sure you have SQL Server Data tools checked while installing Visual Studio.

SQL Server Data Tools

   Comapre Scema

Open VS and select SQL Schema Compare under tools > SQL Server> New Scheme Compare

Then, Select Source and Destination Database Source with Catalog name


After Selecting the Source and Database, click on "Compare"

Then the comparison begins and you wait for it to complete. The bigger the DB with respect to Schema, the more time it takes.


After the comparison is complete, you can see the changed items and added items.

Click on the items you want to move to Source to Destination :D 

Click Shift + Alt + G to generate Scripts to update Schema from Source to Destination

The generated scripts will be open in a separate window


You can Run from Visual Studio Or copy-paste the Script and Run in your destination db SSMS.

If you run from SSMS, make sure SQLCMD in enabled.


Conclusion

In today's article, we have seen how we can easily compare schema across different server hosted same Database. Visual Studio's build in SQL Schema comparison is a free tool for this compared to other paid alternatives.

Labels:
Location: Dhaka, Bangladesh

Comments

Post a Comment

Most Loved Posts

How to configure your Availability Group listener to ASP.NET

SQL Server’s availability group Always On feature is great to have features for your Database. Anytime one of your database nodes goes down, your secondary replica will automatically take over. After a failover, your secondary cluster node becomes the primary cluster. Now the question arises, “Do I need to configure my APP server connectionstring each time I face a failover cluster?”. The answer is NO, you don’t have to configure your app server connectionstring every time. Default ConnectionString By default, your App server connectionstring looks something like this – <connectionStrings>    <add name="ConnStringDb1" connectionString="Data Source=localhost;Initial Catalog=YourDataBaseName;Integrated Security=True;" providerName="System.Data.SqlClient" />   </connectionStrings> ConnectionString for Failover Partner You can manually specify the failover partner in your connectionstring like this <connectionStrings>     <a...
Post a Comment
Read more

Intelligent Query Processing in SQL Server 2019 Big Data

SQL Server 2019: Intelligent Query Processing SQL Server 2019 ships with some brand-new features. Many of these features are targeted for Big Data Solutions. No wonder in that, since the world is moving faster towards Big Data and it is absolutely necessary to cope up with that. Today we will discuss one such feature called Approximate Query Processing. Approximate Query Processing SQL Server ships with Intelligent Query Processing out of the box with SQL Server 2019 installation. Approximate Query processing is a part of Intelligent Query Processing. Things we will be covering in this article – Understand the need for Approximation with Case Study Case Study 1: Railway Case Study 2: e-commerce How to use Approximate Query Processing Demo Code for Comparing Performance Results Limitations When to avoid Approximate Query Processing Understand the need for Approximate Query Processing Before using any technological feature, we must understand why we should use it? Should we jus...
Post a Comment
Read more