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

SQL
69 Comments

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)

, , , ,
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

  • Thanx. This really helped :)

    Reply
  • khurram saddique
    September 27, 2010 12:42 pm

    hi
    i have problem generating script
    well i have a database in sql server 2000 and want to use in sql server 2005 or sql server 2008
    in mssql 2000 one of the table, im using nchar
    now when i use sql publishing wizard error occurs
    do i have to change nchar
    and if i had to what will be the datatype

    Reply
  • Hi Pinal,

    Can you an example script for 2005 on a remote server?

    Thank you,

    Mark

    Reply
  • I installed and using graphical tool and command line while executing I am getting this error. I hope u can help me.Thanks in advance.

    Error: Could not load file or assembly ‘Microsoft.SqlServer.BatchParser, Version
    =9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its depe
    ndencies. The system cannot find the file specified.

    Reply
  • Hi Pinal,

    Thanks for the detail. Can you throw some light on how to script index columns and re-create for all tables in a database.

    Thanks again in advance

    Regards
    K. Sankara Raman
    [removed phone number]

    Reply
  • How to generate script of of a database with data in tables

    Reply
    • If you use version 2008, there is an option. Otherwise you need to use import/export wizard to export data

      Reply
  • Kumaresh Chandra Baruri
    April 12, 2011 11:51 am

    Nice tool. It saves lot of time for me. I wanted to prepare a tool . Then I searched by googling and found.

    Thanks

    Reply
  • I want to deploy the Database changes around in 100 production databases. It is not possible to compare and deploy. Because all databases are not upto date as per the program. So I want deploy the stored procedures and views in the following way.

    if the view/sp exists – Alter the that otherwise create that. I searched for a tool which generate alter & create based on if exist condition. I could not find one. please help me if anybody come to know about such tool.

    Reply
  • I’m having a problem with the SqlPubWiz scripting the create views before the create tables which those views reference. Obviously this doesn’t work since those tables are not found until later. I’ve seen other people have problems with this but have yet to find a solution other than manually rearranging the output file.

    Does anybody have a solution to this problem? Does anybody know WHY on earth it would create the views before the tables? I can see NO reason to create the views first and every reason to create them last.

    Reply
    • Bill

      Having the same issue reported by Jim on 9/23/11. The only solution that comes to mind is to script out the tables, views etc seperately and then run the script for views after the tables script etc.

      Reply
  • blahblah1233445
    January 12, 2012 1:03 pm

    create procedure cyclic_order(@n int)
    as
    declare @i int
    declare @count int
    set @count = 0
    BEGIN
    while (@count < @n)
    begin
    set @i = 0
    while (@i < 10)
    begin
    print @i
    set @i = @i + 1
    end
    set @count = @count + 1
    end
    END

    Reply
  • When I tried creating a schema for one of my databases, I got the below in the command prompt. Can anyone tell me why, Is there any limitation in the database size or number of objects while generating a schema script this way?

    SqlPubWiz Command Line: Error: Operation is not valid du e to the current state of the object

    Thanks,
    Pradhan

    Reply
  • Thanks for this. Two important problems i found using this with SQL Server 2008. But finally i am glad that it worked.

    1. The server should be referred as (Local)\, without local if you refer with your machine name, it will not work.
    2. The database name after -d is case sensitive, otherwise it will not work.

    The following example is for creating a blank database.

    “C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.2\sqlpubwiz.exe” script -S (Local)\ -U sa -P 1 -targetserver 2008 -d c:\mbwindemodbfile.sql -schemaonly

    Thanks.

    Reply
  • hi. how can this generated script be use to convert it to mysql?
    can anyone help me. thnaks

    Reply
  • this tool is given by microsoft, but pinal u give the idea how to use it. great. thank for the help..

    Reply
  • Himmat Jadhav
    March 16, 2012 3:11 pm

    hi pinal ,
    how to remove timout expire error from sql server 2000
    pls give me solution

    Reply
  • whats for Remore server

    Reply
  • Hi Pinal,

    Can you please share your inputs on how to generate SQL Database schema and Data scripts, in an automated process, without using any tool or wizard.

    I mean from the command prompt….

    Please do mail me those details.. it would be of great help…

    Your efforts would be greatly appreciated.

    Thanks in advance.

    Reply
  • Really thank for this help

    Reply
  • Hi, Not able to find script data option in sql server 2008, please help

    Reply
  • Hi, i have seen one issue during schema generation through GenerateScript wizard that some tables schema is not getting generated.that table is present in database and also GenerateScript wizard is showing that table schema will be created.

    Reply

Leave a Reply

Menu