SQL Server Data Tools - How do I preserve data along with schema?

Question

I have been using a SQL Server project to manager the structure of a database.

First I created the project, then imported a database.

Then, when I need to make a schema change, say change a field name, I do it in the SQL Server Project, and then publish it to the actual database using the Schema Compare Option.

I would like to take this a step further to hold basic data that the database needs. Say for example a OrderType table with 2 records "PHONE", "WEB"

This data will be needed on all new instances of the database. Is there a way to keep these in the SQL Server project as well so that they don't get lost?

It seems the only way to do this now, is to keep an actual copy of the master database with the metadata in it, and then use a data-compare. But it would be great if the data could just get published at the same time as the schema so the resulting database is complete.

Answer 1

There are two ways to preserve static data and publish it with a database.

1. Have a "reference" database with static data populated. At the time of publishing a new instance, SQL Server Data tools has a "Data Compare" tool which allows you to compare to live databases, and creates a custom script to update one database with data from the other.

2. Create scripts that contain insert statements, and then run these scripts at publish time. SQL Server Data tools has two tools to assist in this.

   a. Open the data table (right click on SQL Server object explorer, and select "View Data"), and then click on the "Script" button at the top. It will create an insert script for all rows in the table. More on Comparing Data from MSDN

   b. Then take this created script, and add it to the Database Project as a "Post Deployment" script. When you create a publish script for the database, any Post Deployment scripts in the project are automatically included in the master script. More on post deployment scripts from MSDN