Visual Studio: SQL Server Database Projects Part II

Intro

Last week in Part I of this 2-part series we saw how easy it is to setup a database project in Visual Studio, allowing us to treat databases similarly to how we treat our other code. Because our creations and changes are represented as .sql files, we can source-control our database systems which makes releasing and deploying our systems just a little bit easier and more predictable. Last week we saw how to create a new database via one of these projects. This week, we'll see how to create a database project for a database that already exists, as well as how to compare your project schema to the target database's schema. Hold on to yer hats! Or if you're Dan, eat a hat.

Hook up to an Existing Database

If you created a solution file last week for the databases in Visual Studio blog, open that solution file back up. If not you can just create a new solution, however the instructions given here are using last week's solution file as the base. You may have to make slight modifications to your own steps if you create a new solution but I bet you can get there. I also have a pre-made database named BlogLinq.ZooContext. This is one we made many moons ago in a blog posting far far away. If you don't have a pre-made database you can hook up to then go create one real quick and add a small table or two to it. OK, on with the show!

Right-click your solution in the Solution Explorer and add a new project. Select "SQL Server Database Project" as the project type, name it whatever you want, and click OK. You should have a new project in your solution.

Right-click on the new database project and select Import-->Database.

On the "Import Database" screen that comes up, click the "New Connection..." button. Type in the proper information for your connection and click the "Test Connection" button. Assuming things work out OK, click the OK button.

This takes you back to the "Import Database" screen where you can click the "Start" button, which will import your database schema into the database project. If all goes well, you now should have new folder(s) and within those some new sql files that represent your tables. Cool huh?

Schema Comparison

Now that we've seen how to create a database project from an existing database, what do we do with it? As you can imagine, we're going to want to make changes to the schema and eventually deploy said changes. I'm going to open up the Shows table and add a new column for the time of the show. Here's my new table structure:

I've added a column, so obviously this definition is different from the live database schema. Let's prove it by comparing the schemas. Right-click the project in Solution Explorer and select "Schema Compare...".

This brings up the schema comparison window. On the left side is your database project. On the right-side is nothing! Open up the drop-down and choose "Select Target...".

This brings up the "Select Target Schema" window. Select your database and click the "OK" button.

Now click the "Compare" button.

You are presented with the results of the comparison. Yippee! It can tell that we've added a new column to the Shows table named showtime. This is so freakin sweet. I don't want to get too in-depth on what you can do from here, but if you take a look at those other buttons in this window you can see there is an option to deploy the changes, generate sql scripts for later deployment, view unchanged items, and more. Have fun!

What's Next?

Play around with some of the other options available to you. Make a couple databases, update them, etc. It's fun! If you have any questions or comments, leave them in the comments section below and I'll be happy to help out.

Resources

SQL Server Database Development in Visual Studio