Click here to Skip to main content
Click here to Skip to main content
Go to top

SQL SERVER - 2005 - Generate Script with Data from Database - Database Publishing Wizard

, 16 Nov 2007
Rate this:
Please Sign up or sign in to vote.
SQL SERVER - 2005 - Generate Script with Data from Database - Database Publishing Wizard

Introduction

I really enjoyed writing about SQL SERVER - 2005 - Create Script to Copy Database Schema and All The Objects - Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects. Since then, I have received questions about how to copy data as well along with schema. The answer to this is Database Publishing Wizard. This wizard is very flexible and works with modes like schema only, data only or both. It generates a single SQL script file which can be used to recreate the contents of a database by manually executing the script on a target server.

The pre-requisite for Database Publishing Wizard is .NET 2.0 Framework, SQL Server 2005 Management Objects, SMO. The Database Publishing Wizard will script all objects that the supplied User has permissions to see in the source database. Any objects created "WITH ENCRYPTION" cannot be scripted. If such objects exist in the source database, the tool will not produce a script.

First of all, install Database Publishing Wizard: Download Database Publishing Wizard.

It will be installed at the following location: C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\.

Now login using Command prompt and run the following command on any desired database. It will create the script at your specified location. The script will have schema as well as data which can be used to create the same information on the new server.

Command to run which will create schema and database:

C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz 
script -d AdventureWorks "C:\AdventureWorks.sql" 

Command to run which will create schema:

C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz 
script -d AdventureWorks "C:\AdventureWorks.sql" -schemaonly 

Command to run which will create data:

C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz 
script -d AdventureWorks "C:\AdventureWorks.sql" -dataonly 

Command Windows will generate output of action it is taking. See the below two screen shots:

If you have followed this tutorial exactly, you will end up with adventurework.sql which will be quite big and if your computer is not powerful enough, it will hang your computer for a while. I suggest that you try this on a smaller database of a size of around 100MB.

Reference: Pinal Dave (http://www.SQLAuthority.com), Database Publishing Wizard

History

  • 17th November, 2007: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

pinaldave
Founder http://blog.SQLAuthority.com
India India
Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 2200 articles on the subject on his blog at http://blog.sqlauthority.com. Along with 8+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is co-author of two SQL Server books - SQL Server Programming, SQL Wait Stats and SQL Server Interview Questions and Answers. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.
Follow on   Twitter

Comments and Discussions

 
GeneralMy vote of 5 Pinmembersachin.m.jagtap21-Dec-12 19:13 
QuestionGive ur Suggessions PinmemberDevinder kaur Bains3-May-12 18:23 
GeneralGood Article Pinmembercdbh19-Nov-07 6:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140921.1 | Last Updated 17 Nov 2007
Article Copyright 2007 by pinaldave
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid