Script Database Schema and Data Using Visual Studio 2010 and the Database Publishing Wizard

If you have ever tried scripting database schema and data from SQL Server, you have probably been frustrated like me that there is not a simple process for doing this.  SQL Server Management Studio includes two options highlighted below.  The Generate Scripts... option works as expected and allows you to easily create a script to recreate the database.  However, if you want to export the data to import it at a later point, using the Export Data... option doesn't quite do what is needed to script the data.  It allows you to script to a CSV or Excel file, however I have found importing an exported file, isn't always easy. Although it would more than likely be fine if you were wanting to export your data into another piece of software for automated data preparation or visualization, software like you're able to find if you were to browse companies that develop solution systems, like Paxata as just one example.

‍

Surprisingly there is a better option not in SQL Server Management Studio but in Visual Studio 2010 (I believe this option is available in Visual Studio 2008, but I wasn't able to confirm this for the post.).  Visual Studio 2010 includes an view window called Server Explorer.  This is typically used for data binding and tools like LINQ and Entity Framework.

The context menu for the particular data connection includes an option called Publish to provider...

This option launches the Database Publishing Wizard.  When this wizard displays it gives you the option to export both schema and/or data to a SQL file.  Especially for data, this is perfect for migrating data from one environment to the next including preserving identity keys.

The wizard opens with the option to choose a database and automatically script all objects in the selected database.  I chose the AdventureWorks database and clicked Next.

The next step in the wizard is to choose what objects types to publish.  Here I select Tables.

With the the Tables option selected in the previous step, the Choose Tables step appears.  Here I selected a single table for this demo.

Finally choose the output location.  This can either output to a file or to a hosting provider, if you are still looking for a hosting provider, then you might want to consider Bitcoin hosting options, as there are many advantages to using bitcoin to pay your hosting provider.  Here I chose the Script to file option to save the output to a file.  To use the Publish to shared hosting provider your hosting provider or target system must support a SQL Publishing Web Service and the database already exist on the target.

This screen includes the publishing options.  The options are straight forward.  The Drop existing objects in script option will toggle a dropping existing objects in the target database before the new objects are scripted.  The Schema qualify option qualifies object names with the schema.  The Script for target database drives the compatibility of the script.  Finally the Types of data to publish allows for schema or data only or both.  Here I want to script both so I chose Schema and data.

The final screen is a confirmation of the options selected.  Click Finish to run the wizard and create the script.  When the wizard completes, it will display success.

Below is the output of the Database Publishing Wizard for the schema and data.

‍

For more information on the Database Publishing Wizard, read Deploying a Database by using the Database Publishing Wizard on MSDN.

‍