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.

screenshot-2008-05-05_18_56_00

Now click File -> Add -> New Project.

screenshot-2008-05-05_18_57_08

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.

screenshot-2008-05-05_18_58_44

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.

screenshot-2008-05-05_18_59_29

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

screenshot-2008-05-05_19_01_31

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

screenshot-2008-05-05_19_02_07

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

screenshot-2008-05-05_19_02_15

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

screenshot-2008-05-05_19_02_31

Now your project should look something like the shot below.

screenshot-2008-05-05_19_02_51

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.

screenshot-2008-05-05_19_05_20

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

screenshot-2008-05-05_19_05_31

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

screenshot-2008-05-05_19_05_52

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.

screenshot-2008-05-05_19_06_51

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.

screenshot-2008-05-05_19_07_18

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

screenshot-2008-05-05_19_07_26

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

screenshot-2008-05-05_19_08_07

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..."

screenshot-2008-05-05_19_10_15

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

screenshot-2008-05-05_19_10_27

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.

screenshot-2008-05-05_19_12_20

screenshot-2008-05-05_19_12_31

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.