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.

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.

Comments

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...

SQL Insider 01 : An Anatomy of SELECT

Introduction When we write queries, we tend to think about the internals very little. In the new series of SQL Insider, I shall try to demonstrate what your SQL Server has to go through when you write a specific query, more specifically a specific operator. In the series, we shall try to cover all the important operators in SQL. Our today's SQL participant in SELECT. SELECT With the SELECT query, we can select one, some, or all the columns of a SQL table. The typical syntax for SELECT is like this SELECT * FROM Sales.SalesOrderDetail SELECT sod.OrderQty, sod.UnitPrice FROM Sales.SalesOrderDetail sod Please note that we will not be dealing with WHERE clause in today's episode. Database We will be using AdventureWorks2019 Database for the demonstration Important Configuration We will be setting STATISTICS IO ON like this - SET STATISTICS IO ON; SET STATISTICS IO ON ; We will turn on Actual Execution Plan to examine the query SQL Insider Let's sta...

How to encrypt an entire excel file using AES-128 encryption

What Is AES? AES stands for Advanced Encryption Standard. It is a symmetric block cipher that is used by the U.S. government to protect classified information. AES is used worldwide to protect classified data around the world. AES is essential in cybersecurity, electronic data protection, and computer security. Variations of AES AES is used in three block cipher versions namely AES 128, AES 192, AES 256. AES 128 uses 128-bit key length to encrypt and decrypt block messages. This is a symmetric secret key which means it uses same secret key for encrypting and decrypting message blocks. AES Encryption of Excel File Today we will AES 128 to encrypt an Excel File. The excel file looks like the following image. To encrypt this excel file, we will use C#. First, we need to create a console project. Let's name the project ExcelEncryption. First, we try to understand what we need to achieve. We need a KEY and Initialization Vector (IV) pair for AES 128 encryption. Let’s say, th...

SQL Data Tools - Compare Data

Compare Data between two tables SQL Server Database with the same schema architecture can differ in different environments like Dev, Staging, and Production, especially in configuration tables. Let's see how we can easily sync the data in two different tables.

SQL Server Performance : OR vs UNION ALL

When writing queries, we seem to care less about the performance issue at first. Our first goal is to make the output right. When we get the correct output, we then move on to the next phase we call performance tuning. Today we will try to understand what happens when you write OR in your query. We will do the same thing with UNION ALL and try to understand which one seems to perform better.

SQL Server Pain Relief

Search This Blog