Using Database Projects for Visual Studio

Do you use database projects in Visual Studio? If not, then now is a good time to start. It is the best way I have found to source control my databases without actually sticking the database file itself in the repository. It is very simple to add to your solution and creates a default directory structure for your create scripts, change scripts and queries. Best of all the project will be recognized by source control and added to the source code repository. This means you can keep versioned scripts for maintainability. It also gives you the option to do the dreaded rollback!

Lets start by creating a database project of our own. To start, we will create a blank Visual Studio solution.

Now click File -> Add -> New Project.

You should get the dialog below. Now we will look in the node of "Other Project Types", click "Database" then select "Database Project". This will give us the opportunity to name our database project. We will call this one by the database we are going to be using, good ol' Northwind.

Next you will be asked to setup a database reference. So here all we have to do is add a new reference to the Northwind database.

My database will be in SQL Server, yours can be in any of the choices shown below.

Now we just set the server name and choose the Northwind database.

You should then test your connection (out of good practice) and see the dialog below upon success.

Now we will see our database reference shown in the original list of references. Just double click on your newly added reference.

Now your project should look something like the shot below.

Once we get this far, we need to start filling our folders with database scripts. You can add new folders if you wish, but I think the default folder structure is good enough. Now then, we can use the database publishing wizard to get our first create script into the "Create Scripts" folder.

Double click the reference to your database in the database references and it should bounce you to the server explorer window with your data connection all setup.

Right click your data connection and click "Publish to provider..." to start the database publishing wizard.

Now we select the database we would like to publish, so again I will choose Northwind.

Now with the "Script to file" option chosen, we will browse to the "Create Scripts" folder in our database project to put the outputted .sql file.

Now we get to choose some options. I am going to set the output for SQL Server 2005 and select to script the database schema only.

Now we can review the summary of what we have configured and click finish.

Upon clicking finish, we will see the progress of the .sql file being scripted to the hard drive.

Ok, for some reason I couldn't find a refresh button in the database project to see the file that was created so we will right click the "Create Scripts" folder and click "Add Existing Item..."

Then browse to our .sql file and click the "Add" button.

Now we should see it in our project and we can also see the contents of the generated script by opening it in the editor.

It really is just that simple. I mean if nothing else you have a designated place to put all your SQL scripts for your database. The big plus is that your scripts will be source controlled. Also, anyone who opens the solution will know exactly which databases are used by the project.