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.  

Figure 1 - SQL Server Management Studio

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.

Figure 2 – Visual Studio 2010 Server Explorer

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

Figure 3 – 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.

Figure 4 – Select Database in the Database Publishing Wizard

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

Figure 5 – Choose Object Types in the Database Publishing Wizard

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

Figure 6 – Choose Tables in the Database Publishing Wizard

Finally choose the output location.  This can either output to a file or to a 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.

Figure 7 – Select an Output Location in the Database Publishing Wizard

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.

Figure 8 – Select Publishing Options in the Database Publishing Wizard

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.

Figure 9 – Successful Database Publishing Wizard

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

Figure 10 – Schema output of the Database Publishing Wizard

Figure 11 – Data output of the Database Publishing Wizard

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

This was also cross posted at http://www.deliveron.com/blog/post/Script-Database-Schema-and-data-using-Visual-Studio-2010-and-Database-Publishing-Wizard.aspx

Enjoy!

Mike Douglas