Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Schema Compare for SQL Server

DZone's Guide to

Schema Compare for SQL Server

If you need to do a schema compare but are limited in your ability to purchase or install tools, the FC command is a brilliant option that you have available in Windows.

· Database Zone ·
Free Resource

Built by the engineers behind Netezza and the technology behind Amazon Redshift, AnzoGraph is a native, Massively Parallel Processing (MPP) distributed Graph OLAP (GOLAP) database that executes queries more than 100x faster than other vendors.  

SQL Server Management Studio (SSMS) does not offer the ability to do a schema compare between two databases. Therefore, you have two options to do a schema compare for SQL Server databases. The first is to use Visual Studio. The other is to use a third-party tool. However, there are two issues with either of those options that affect a lot of data professionals and DBAs:

  1. You aren't allowed to install tools on your work machine.
  2. You don't get any budget to purchase tools.

Today, I'm going to show how to do a schema compare using only SSMS, dacpac files, and the FC (File Compare) command available in Windows. As always, you're welcome.

Create a DACPAC

First, create a dacpac for each database. I am going to use AdventureWorks2008 and AdventureWorks2012 as my examples for this post. You create a dacpac by right-clicking on the database name in SSMS, selecting Tasks, then Extract Data-tier Application.... No, I don't know why they couldn't name it something easier, like Create DACPAC. But I digress. Here's what it looks like:

Click through the wizard, pick a location to save the dacpac files, and wait for it to finish.

The dacpac files aren't useful in their current format at the moment. We will fix that next.

Crack Open DACPAC Files Using This One Weird Trick

That's right, all you need to do is rename the files from .dacpac to .zip, then extract the files. It's that simple. Once you do, you will find a handful of files. We are going to focus on model.xml today. I encourage you to poke around the origin.xml file to see the goodness inside there.

Open a Command Prompt

Yes, I said a command prompt. I know this is possible with PowerShell. More on that later.

Windows comes with the FC command, available from the command line. Open a command line and run 'help FC' to see the list of available switches.

We will use the /L and /N switches and compare the two schemas, outputting to a file.

FC /L /N AdventureWorks2008\model.xml AdventureWorks2012\model.xml > output.txt

The compare produces an output file that looks like this:

A quick check confirms that the PK name for the HumanResources.Employee table did change between the 2008 and 2012 versions of AdventureWorks.

Summary

If you need to do a schema compare but are limited in your ability to purchase or install tools, the FC command is a brilliant option that you have available in Windows.

If you are a PowerShell aficionado then you don't need me to tell you that the Compare-Object and Get-Content cmdlets can get the same job done. I will leave you with two thoughts on that subject. First, the FC command is a lot less typing, and I'm lazy. Second, PowerShell isn't the answer here, and neither is a command line. The real answer is to use Visual Studio to do the job right.

Download AnzoGraph now and find out for yourself why it is acknowledged as the most complete all-in-one data warehouse for BI style and graph analytics.  

Topics:
database ,sql server ,schema ,schema compare ,tutorial

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}