Visual Studio 2012 SQL Database Schema Compare

Author

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:

VS1

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”:

 VS2

This is the window that opens:

 VS3

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: VS4

 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:

 VS6

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

 VS8

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:

 VS9

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:

 VS10

Just as we expected, the two databases are identical:

 VS11

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

 VS12

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): VS13

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:

 VS14

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

 VS15

Our script is created:

VS16

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:

 VS17

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

VS18

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

 VS19

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.

  • Austin Christenberry

    Great post, Kevin! Extremely helpful.

  • Matt Levy

    love the post Kevin, thank you!

  • http://www.cloudstaff.com/ Alta Noble

    This is indeed a helpful post! Thanks for sharing.

  • http://www.process-box.com/ Clarissa Lucas

    Thank you for posting such an informative post. The information are truly useful. Cheers!