Contact

Technology

Jun 24, 2013

Visual Studio 2012 SQL Database Schema Compare

Kevin King

Kevin King

Default image background

How many times have you been in the middle of a project and you have that gut wrenching realization that there’s something different between your database environments, but you have no idea what it is? Well, hopefully that doesn’t happen to you very often (because you are a best practice machine), but in the real world this can happen – and sometimes it’s not your fault.

There are plenty of third party applications and software bundles that can provide solutions to this problem. A few vendors come to mind, but we’ll leave it fairly generic – they are out there. As a Microsoft developer, whenever I start using third-party software, a little voice in my head tells me I’m doing something wrong. Most of the time Microsoft has your back and you’re going through a lot of trouble for nothing. It took me a long time to figure this one out, but I’m extremely glad I did.

Visual Studio will fix this database disparity dilemma for you. Really though, are we surprised?

Let’s check out a test scenario to understand what capabilities Visual Studio has dropped in your lap. Imagine you have two databases and are a very creative person:

OK, you’re not very creative.  But you do have two databases.

Now let’s assume it’s your job to make sure these databases are in sync.  Hold on there, buddy! I know what you’re thinking – but no, you cannot just backup and restore to Database Two. In fact, you need to retain different data sets in each database, but the schemas must always match.

Visual Studio SQL Database Schema Compare to the rescue!

Let’s set the stage for our hero.

Create a Persons table in both databases with the same create script:

CREATE TABLE [dbo].[Persons](

                  [FirstName] [varchar](100) NULL,

                  [LastName] [varchar](100) NULL,

                  [Email] [varchar](100) NULL,

                  [FavoriteSport] [varchar](100) NULL

) ON [PRIMARY]

Now open up Visual Studio 2012 and create a blank project (or any project, it doesn’t matter). Next, in the main menu, select “SQL,” then “Schema Comparer,” then “New Schema Comparison”:

 

VS3

This is the window that opens:

 

Notice we are presented with two drop down menus labeled “Select source” and “Select Target” (why their capitalization is different, I do not know. I digress…).

Let’s connect our source and target databases:

VS5

 

We are given the option to connect to a database that is already in our solution or create a new connection. If you’ve already set up your databases, select the source and click “OK”. If you are following this blog step-by-step, you have not, and will need to click “New Connection.”

Because my SQL server instance is running locally, I can simply type a period “.” in the Server name field (free tip alert!) and select “Database One” from the database drop down:

 

VS8

Click OK, then OK again, and now we have selected our source:

 

Let’s do the same for the target database, but instead select “Database Two.” Now we have both databases selected and can begin the magic:

 

VS10

You guessed it! Click the “Compare” button.

Toward the bottom of the open window, we see that Visual Studio is thinking very hard about our Persons table:

 

Just as we expected, the two databases are identical:

 

VS12

Let’s go make some changes to Database One by adding a column to the Persons table:

 

Highlighter software sold separately…

Now, let’s go back to Visual Studio and do another comparison by clicking “Compare.”

Viola! Visual Studio has detected a schema difference and has highlighted it in the object definitions window (which is just the create script for the database object):

VS14

If you’ve used Team Foundation Server (TFS), this probably looks really familiar to you. They even provide a slick visual navigation tool on the left to quickly scroll to where the difference are. This isn’t very useful for us because our tables are small, but you can imagine the usability upgrade for very large and complicated database schemas.

At this point, we have two options to get our databases back in sync. We can either generate a script for the target database by clicking the script icon or we can let Visual Studio do the work for us:

 

Let’s generate a script first. We can monitor the Data Tools Operations window to see progress:

 

VS16

Our script is created:

Instead of running this ourselves, we will just let Visual Studio do the work. It’s not a bad practice to use the script generation as a sanity check before you click the magic “Update” button and it will likely save you some serious drama at some point.

Click “Update.” Yes, we’re sure:

 

VS18

In our Data Tools Operations window, we see that the process has been completed:

To double check, we can go into SQL Server Management Studio and look at both databases, or we can simply run another compare:

 

That’s it!

Things to note

–        If the schema differences produce relationship or nullability conflicts, you will need to take a few extra steps and possibly some manual tasks to get it right.

–        It’s a good idea to save this compare file in your solution so you can run it later without having to configure it.

–        Only you can prevent broken builds.

Looking for more great development tips or the latest Microsoft news? Follow @CrederaMSFT on Twitter.  Or have suggestions for new blog topics? Leave us a comment below and we will do our best to get you an answer.

Conversation Icon

Contact Us

Ready to achieve your vision? We're here to help.

We'd love to start a conversation. Fill out the form and we'll connect you with the right person.

Searching for a new career?

View job openings