By Derek Du

Yesterday I was wondering, what is the best practice to manage database changes in TFS? In the past we have been using CVS to do version controls. The way to manage database changes was just having a folder to store all the scripts from the day the project starts. So when it comes to TFS, the same idea can be applied – create a folder of database scripts in the solution.

Here are the screenshots everyone wants:

Create a Database Project in VS2012. Notice that in 2012 they have get rid of “Server Project”. Part of that is because in VS 2012 you don’t need to have a Server project to create server level objects such as Login object. Server objects can be directly added to Database project in VS 2012 via Add -> New Item. option. More about this can be found later in this post.

Now we have the project created.  We can start adding objects to the project.

If you have a database already, there are two ways that scripts of an existing database can be imported to the project:
1. Import.
2. Compare Schema and update your project with only the selected objects. (Source database is the existing database and Target database is the project.)

Here is how the Schema Compare looks:

Troubleshooting:
1. Unresolved reference
If you have objects from other database, right click on the project and select “Add Database Reference”, OR
Add the referenced objects to the project. Login object is an example. It would not be imported because it is at server level but you can manually create it.

2. All I see are errors.
Sometimes after importing a huge database developed outside of the VS, one may experience a lot of errors and warnings. A simple workaround is to not import the database, and use the project to just manage new scripts. This is a more traditional approach to me.
There is just one thing to keep in mind. When adding new scripts, select Script (Not in Build). Otherwise it will check for errors in the scripts, and because the rest of the database objects are not imported, there will always be errors.