If you haven’t had the opportunity to use a Database Project in Visual Studio yet and you manage any size of database for an application, have I got a TIL for you!
Database projects (.dbproj) in Visual Studio provide a great way to manage and deploy changes to your databases in a way that’s familiar to anyone who’s used SQL Server Management Studio. In fact, the project setup mirrors exactly what you’d see if you were to open a database with SSMS. You simply write the scripts to deploy tables, stored procedures, user-defined functions, and all that other good database stuff.
What’s different with database projects is the few in-between steps that Visual Studio does for you. For example, if you introduce a change that breaks a table dependency or constraint, normally you’d find that out when you go to deploy your changes to the database. With database projects, Visual Studio actually “builds” your database schema and verifies everything before it touches your database, allowing you to intercept those errors before they become a problem during deployment.
Because VS has a “build” of your database, you can do a lot more with the schema than you normally can, including things like database unit testing (this will be a TIL in the future), refactoring, visualizing dependencies, comparing against live databases, and more.
Perhaps the best part of a database project, when you go to deploy (or generate a deployment script) you can point it to an existing database project and it will automatically analyse your project’s structure against the existing structure and generate the appropriate deployment script based on the differences. If there are any errors that would prevent deployment, you’ll find those out beforehand.
Of course, you can check all this into your source control and manage changes just like you would any other change to your codebase. For more information, check out the MSDN Docs