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

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.
1 comment
Read more

How to deal with Slow SQL Server due to Autogrowth issue

  Why you should not stick to SQL Server’s default Initial file size and autogrowth We hear a lot of these statements : My SQL Server is running slow My Production DB was fine when we started, But it is staggeringly slow now My Business end users are frustrated to wait too long Well, there are lots of reasons why your SQL Server might be slow. Setting the Autogrowth option to default is definitely one of the vital ones which we seem to ignore most of the time. Slow SQL Server and Tortoise SQL Server provides you with some default settings for autogrowth when you install it for the first time. These default cases are defined with increment by 8MB or by 10%. You need to change it to suit your own needs. For Small application, this default value might work but as soon as your system grows, you feel the impact of it more often. What Happens SQL Server Files needs more space SQL Server Requests the Server PC for more space The Server PC takes the request and asks the SQL request...
Post a Comment
Read more