Managing your database development has become much easier with the recent versions of Visual Studio. Visual Studio can be used to create new databases, create new data-tier applications, and maintain existing databases or data-tier applications. If you want to manage change to your database, using a database project and source control is the way to go. You and your team members can check out, add or modify files, build, and test changes to the database in an isolated sandbox environment before promoting or sharing with the team. The next few sections will outline how to create a database project with Visual Studio 2012.
Now, one of the easier ways to go about creating the database project is to start by creating a new database. In the following steps, I am going to use SQL Server and SQL Server Management Studio to initialize my new database before moving into Visual Studio and creating a database project.
First off, I open SQL Server Management studio and create a new database. Once the database is created, I add a new schema and create a new table that belongs to the schema just created. The table can be very simple (i.e. one column). The idea here is to have the table to belong to the new schema so the project is structured properly in Visual Studio after the database is imported.
Next, open Visual Studio 2012 or above, and create a new SQL Server database project (found under ‘Other Languages –> SQL Server’). Now that the project has been created, its time to import the database. To do so, right click on the database project and select import. Next you will want to enter your source connection string and specify your import settings as seen in the image below.
Once the database is imported, a folder will be created for each Schema. Objects belonging to the schema are placed in folders under the schema as seen below.
Now that the database is imported into the project, you can continue to build out the database objects in the project. For example, to add a table, right click on the schema folder and select add new table.
Now you can add the columns and constraints for the table in the table designer. Also a side note, be sure to correct the schema name in the generated script to the appropriate schema (highlighted below).
Once your ready to apply your changes to the database, you can publish the changes by creating a local publish profile. The profile can then be used by all developers needing the database to code against. To create the publish profile, click on Build, then ‘Publish [your database name]…’ as shown below.
In the Publish Database window, click ‘Edit’ to set the database connection properties.
Next, specify the Database Connection properties
Now the Publish Database window should look similar to the image below with the connection details completed.
Next, click ‘Save Profile As…’ to save the publish profile and give it a meaningful name that your team members will recognize as well.
Next, click Publish and your database will be published to the database set in the connection string.
In the Data Tools window you can see the status of the publish action to your local database.
To confirm the changes you made in Visual Studio are propagated to SQL Server, open SQL Server Management Studio and review the database to see the changes made to the database project are now reflected in SQL Server.
Now that you have a database project, you can add it to source control as you would any other project and share with the rest of your team members.