SQL SERVER – Generate Script with Data from Database – Database Publishing Wizard

SQL SERVER - Generate Script with Data from Database - Database Publishing Wizard 31-800x450 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 the I have received question that 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.

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

Now login using Command prompt and run following command on any desire database, it will create the script at your specified location. Script will have schema as well as data which can be used to create the same information on 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 below two screen shots of it.

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

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

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 size around 100MB.

Reference : Pinal Dave (https://blog.sqlauthority.com)

DBA, SQL Backup, SQL Download, SQL Restore, SQL Scripts
Previous Post
SQLAuthority News – Microsoft SQL Server 2005 Assessment Configuration Pack Download
Next Post
SQL SERVER – 2005 – Best Practices for SQL Server Health Check

Related Posts

69 Comments. Leave new

  • Hi All,I want to publish database to godaddy using database publishing wizard but when i connect with server name and windows authentication it does not show all databases.it shows only master,msdb and tempdb.I am totally new to it can somebody help.

    Reply
  • thank you

    Reply
  • How do i Generate scripts for a database form sql 2000 to sql 2014

    Reply
  • The script generated in sql server 2005 is support in sql server 2014

    Reply

Leave a Reply