SQL Server Data Publishing wizard for SQL Server databases

Hi Friends,

SQL Server Data Publishing Wizard was one of the most popular downloads for SQL Server 2005. It allows a developer to deploy a local database from his development environment to a hosting provider on a remote system. The wizard creates a single script file which contains the schema and the data. This script file can be used to create an exact replica on the target system.

Remember, I mentioned that this wizard is available since 2007. So it was designed for SQL Server 2005 and requires SMO (SQL Management Objects) for 2005. You can still download this wizard at: http://www.microsoft.com/downloads/en/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

But wait; the story is not complete; since the release of Visual Studio 2008, this wizard is integrated in to Visual Studio IDE. And yes, it means that you have this in VS 2010 as well. So, I am going to show you how you can work with this feature to publish the schema and the data in a single script file for your local database. (What I am showing you will also work with VS 2008 with minor difference in the UI)

Start Visual Studio 2010 and show Server Explorer.

1_Data_Publishing_wizard_for_SQL_Server_databases

In Server Explorer, connect to the database for which you want to create the script. In my example, I am connecting to Northwind database which is located AmitBansal\sql2k8r2 instance.

2_Data_Publishing_wizard_for_SQL_Server_databases

Well, adding a data connection is very straight forward; just right click Data Connections and click Add Connection. Supply the necessary details including Instance name, Authentication details and the database and you are done.

Once you are connected, right click the connection you just created and click Publish to Provider…

3_Data_Publishing_wizard_for_SQL_Server_databases

In the welcome wizard, click Next.

4_Data_Publishing_wizard_for_SQL_Server_databases

In the Select Database page, select the database which you want to script out:

   

5_Data_Publishing_wizard_for_SQL_Server_databases

Observe that you can script out all the objects. If you want to be selective, uncheck the option at the bottom of the page. For this example, let us uncheck this option so that we can script out only a few tables. Click Next.

In the Choose Object types pages, let us select Tables. Click Next.

6_Data_Publishing_wizard_for_SQL_Server_databases

In the Choose Tables page, select a few a tables that you want to script out. (Be careful if you are scripting out a table with millions records 🙂 – For this example, I am only scripting out the Orders tables. Click Next.

7_Data_Publishing_wizard_for_SQL_Server_databases

In the output location, you need to specify the location of the script file. You can directly publish to the shared hosting provider by selecting a hosting provider or configuring a new one. For this example, let us script out in a file. You may overwrite an existing file. Browse and specify your file and then click Next.

8_Data_Publishing_wizard_for_SQL_Server_databases

In the Publishing Options page, is the real meat. You can choose to include the script for ‘drop existing objects’. You can choose to qualify every object with the schema name. You can choose the target database version which could be SQL Server 2005, 2000 or 2008 (the order is strange). Finally, you can choose to script out only the Data, or only the schema or both. Let us go ahead and choose ‘Schema and Data’. Click Next.

9_Data_Publishing_wizard_for_SQL_Server_databases

Review the summary and click Finish. Once done, close the wizard. Review the script file to observe it contains what you want. Do you observe that the script also contains data from some other tables like Customers and Employees. Any takes? Let us interact on this one at the forums.

 

 

   

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

6 Comments on “SQL Server Data Publishing wizard for SQL Server databases”

  1. Hi Sarab, Those options that you showed in your blog post do not appear on my instance. I am using SQL Server 2008 R2 Dev edition. Not sure why the options are not popping up? Which version/edtiion do you use?

  2. Hi Amit,

    I am using SQL Server 2008 R2 x86 Enterprise Edition, the one i got with MSDN subscription.

    But i think this should be available over all editions (as stated by Sachin)

    Thanks,
    Sarabpreet Singh

Leave a Reply

Your email address will not be published.