One of the challenges that every database developer faces is tracking database schema changes. When updates need to be made on a production server, it can be a stressful process. Every schema change made since the last update must be carefully tracked, and correct update scripts need to be generated for the production server. If this process is not done carefully, there is a risk that the application will break after the deployment of the new version.

Traditionally, teams have resorted to manually noting down schema changes every day and creating a consolidated final list for the update on the production server. However, this approach is time-consuming and prone to human errors. A better solution is to use a tool that can compare the development database and production database and generate an update script to be run on the server. This approach is more efficient, less time-consuming, and reduces the chances of errors.

There are several tools available on the internet today for tracking database schema changes. One such tool is Red-gate’s SQL Compare, which is widely used and highly regarded. In this article, we will explore another tool called Visual Studio Team Edition for Database Professionals (DBPro), also known as DataDude, as a solution to the problem of tracking database schema changes and generating update scripts.

Creating a DBPro Project

To begin, let’s create a new Database Project in Visual Studio. Go to File, New Project, and select Database Project. For the purpose of this example, let’s assume we have a development database called AdventureWorksDev. After restoring this database, we have made modifications to a table, view, and stored procedure, as well as created a new table and stored procedure.

Generating Update Script

Now, let’s compare the development database (AdventureWorksDev) with the production database (AdventureWorks) and generate an update script. In Visual Studio, go to the Data menu and select New Schema Compare. Choose the development database as the source schema and the production database as the target schema. DBPro allows you to compare a database or database project with another database or database project.

After selecting the source and target connections, DBPro will start the schema comparison. The results will be displayed, showing the objects present in both databases and the scripts for the selected objects. The update script needed for the database update will be highlighted in green.

Reviewing the Changes

Before updating the production database, it is important to review the changes. In DBPro, you can filter the object list to show only the objects that are new, edited, or deleted. By clicking on an object, you can see the changes in the Object Definition window below. This window allows you to review the location and code that has been modified.

If there are objects that you do not want to update, you can set the Update Action to Skip for those objects. However, please note that DBPro will not allow you to skip an object if other objects depend on it. For example, if you want to skip a table, you also need to skip the stored procedures that use that table.

Updating the Changes

Once you have reviewed the changes and decided which objects to update, you can proceed with updating the target database. DBPro provides two options for this. The easiest option is to click the Write Updates button on the toolbar, which will directly update the target database with the changes. Alternatively, you can click on the Export to Editor button to generate the update script in a new window. You can then execute the script on the target database using the toolbar button or copy the script to SQL Server Management Studio and run it from there.

Conclusion

DBPro is a powerful tool that simplifies the process of tracking database schema changes and generating update scripts. It eliminates the need for manual tracking and reduces the chances of errors. In future articles, we will explore other interesting features of DBPro.