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

« SQLAuthority News – Microsoft SQL Server 2005 Assessment Configuration Pack Download

SQL SERVER – 2005 – Best Practices for SQL Server Health Check »

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

November 16, 2007 by pinaldave

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.

First of all install Database Publishing Wizard from here : Download Database Publishing Wizard.

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.

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

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

About these ads

Posted in DBA, Pinal Dave, SQL, SQL Authority, SQL Backup and Restore, SQL Download, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 70 Comments

70 Responses

on January 24, 2008 at 7:16 pm | Reply Vinay

Can u suggest me how to script the remaining objects of the database excluding the encrypted one’s….as this wizard is not having the capacity to script encrypted objects
on February 1, 2008 at 11:33 pm | Reply minan

why users are converted to roles during the scripting. How to overcome this limitation? Apart from that its a good tool
on February 15, 2008 at 6:05 pm | Reply Thomas

Try SQLScripter (http://www.sqlscripter.com) to script your data. This tool supports all DML commands (Insert, Update, Delete) as well as some other cool stuff (Export to Text/Csv, Excel, Pdf …). Currently, it’s free.

Cheers
on February 19, 2008 at 2:59 pm | Reply Vinay

Please anyone advice …

The scriptor is running fine but it is not scripting the database even if a single object is encrypted. It should actually exclude that encrypted object instead of excluding whole objects in that database.

Can any one advice me how to exclude the object which in encrypted..instead of excluding the entire database…..
on April 1, 2008 at 8:50 am | Reply Tim W.

FYI if you spell the database name wrong this program gives you cryptic errors (like it cannot find the destination folder of the generated script file)
on April 6, 2008 at 2:35 pm | Reply Tim W

This worked perfect. Thanks for posting this.
on April 13, 2008 at 1:14 pm | Reply vinay

how can we use this for database on remote machine..i.e using sql server authentications
on April 28, 2008 at 12:45 am | Reply Fabio Soares

Hello Pindaldave

I tried to generate a script with the publishing wizard, but it didnt work because i should give the username and password in order to access the database server. I want only the data to be scripted. Is it possible in the command line?

Thank you
on June 12, 2008 at 4:50 am | Reply Steve

Hi,

Does anyone know if there’s anyway to use command to generate data on specific tables rather than on all tables? On the Data Publishing Wizard documentation, one of the example shows a -include switch, however, when I tried to use it in command, it complains that -include switch is not recognized. Thanks~
on July 16, 2008 at 4:17 am | Reply stan merrill

I have used the publishing wizard and it works in most cases. for the situations where I have any of the following special characters the inserts fail:
”
crlf
‘
Is there some strategy that I can use to avoid the problems caused by the characters.

The only thing that I can think of is to have a translation before insertion and then translate back after retrieval for all fields for all 3 characters.

Thanks,

Stan
on August 11, 2008 at 2:26 pm | Reply akram

Hi,

In SQL Server 2005, is there any features provided to backup tables and data in *.sql format? exclude any other external tools to perform the backup. I tried the Generate Script features. while i tried to follow the wizard, my database doesn’t appear on the database selection?may i know why?

Akram
on August 20, 2008 at 3:47 pm | Reply Balasubramaniyam

Hi,

We are generally using script generation to get table structures only. With data means backup and restore.
Then Why we need Database Publishing wizard?.

Thank You

Rgds,
Balasubramaniyam
on September 17, 2008 at 9:22 pm | Reply JC

Balasubramaniyam,

Here is a good case for when you want to script the schema and the data, but you do NOT want to backup & restore: You want to reorder the columns in a table!

After you run the wizard, find the scripted CREATE TABLE xxx in the SQL, cut and paste the columns for that table in the desired order, then execute the SQL. The entire database is re-created, the data inserted, and your columns are in the desired order.

JC
on September 25, 2008 at 8:03 pm | Reply Drew

When I script my database to an .sql file, I cant run it on my new server. it seems to be working but the table never gets created and the data never goes in.

What am I doing wrong? I successfully pulled all data from my remote host into the sql file and confirmed that it’s in there, but when I run it as a query against my new DB, I get NADA.

Please help! this is frustrating!
on November 22, 2008 at 2:08 pm | Reply Dude

If we want to generate insert script for inserted data from sql server then what we can do?
Because when we use generate script from sql server at that time it shows only structure of table not the inserted data.
It working threw import/export wizard but if we have foreign key then it not work.
Can any one help me?
Thanks in advance…
on March 19, 2009 at 7:25 pm | Reply sridhar

i have a scrilpt file.i want to generate the database.i.e.
tables ,stored procedures etc.
how can
i do it?
on March 24, 2009 at 9:34 am | Reply Praveen Agrawal

Hi All, How to Generate cyclic order using sq l server
on March 25, 2009 at 4:06 pm | Reply Priyanka Ainchwar

you can create this procedure and get ur output

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
on April 6, 2009 at 7:16 pm | Reply Pavan N

Am looking to find the SQL code which is working behind the Database Publishing wizard/Generate Scripts wizard.

Kindly let me know if someone is having info about this.
on June 9, 2009 at 12:07 pm | Reply ronak

great tool..!

Thank you Pinal.
on July 20, 2009 at 11:02 am | Reply Nitika

This tool solves my Problem.
Great Tool …….

Thanks Pinal :)
on July 22, 2009 at 6:14 pm | Reply Samson

Pinal,

Do you know if they released a publishing wizard for sql server 2008? My research indicates that it is designed for 2000 and 2005, but I could be wrong. I haven’t tried it yet on 2008, but I figured I would search first.
on July 22, 2009 at 6:37 pm | Reply Samson

Pinal,

As it turns out it seems to be a part of sql server 2008 already. I was expecting a separate install!
on August 4, 2009 at 11:19 pm | Reply Kunal Shah

Great Tool !

I was doing backup/restore everytime !!

Saved much time.
on September 16, 2009 at 3:15 pm | Reply giri

how to script table data in SQL Server 2005
on October 13, 2009 at 9:21 pm | Reply Roma

Thanks, super tool!
on October 28, 2009 at 7:00 pm | Reply Ramesh

Great Tool !
on October 30, 2009 at 6:42 pm | Reply Sunny

Hi,

Previously i had SQL2005 and i used always database publishing wizard and it worked fine but now i have SQL2008 and i have searched so much for database publishuing wizard for the same but not able to get it anywhere.

please help me how to generate the script so that i can copy the script onto my online database server as i have so many tables and need to upload only 1 table…

please suggest..

Thanks in advance

Sunny
on October 31, 2009 at 7:52 pm | Reply Bayo

i am a new web signer , i need more understanding about data base, my SQL, data base server and mySqL server. can some tell me more about that ?
on November 13, 2009 at 2:00 pm | Reply vaish

thank you
it helped me
on November 23, 2009 at 3:25 pm | Reply Marcus Nyberg

Thank you, this helped a lot when I used it to backup a database from a machine that I had no permissions to do a backup “the regular ways”.
on December 3, 2009 at 11:24 pm | Reply Fazil

Hi,

I appriciate if anyone can provide me the procedure or the way of Automated process for generating sql script which we generaly use by right clicking on DB then all task then go for generating sql script,

I want the Automated process for SQL2005.
on December 9, 2009 at 6:23 pm | Reply Dhanajeyan

Hi all,
Could anyone suggest me a stored procedure to generate a script for crud operation
on December 12, 2009 at 5:00 pm | Reply Ashwin Ala

Thanks for step by step solution
on January 30, 2010 at 7:02 am | Reply SQL SERVER – Several Readers Questions and Readers Answers Journey to SQL Authority with Pinal Dave

[...] A. In SQL Server 2008 : Right click Database > Tasks > Generate Scripts > In the wizard on Choose Script Option page, set Script Data option to True and complete the wizard.For SQL 2005 or earlier versions, use Database Publishing Wizard. For more details about Database Publishing wizard, please visit the blog http://blog.sqlauthority.com/2007/11/16/sql-server-2005-generate-script-with-data-from-database-data… [...]
on February 6, 2010 at 4:09 am | Reply Judas Gutenberg

Why does everything have to be so difficult in SQL Server? I want to move a large DB from one server to another. In MySQL, I script it out, download the script, upload the script to the new place, and then type in ten or twenty characters to install it EXACTLY as it was. In SQL Server, on the other hand, all the wizards destroy the constraints and relations and there is no easy way to script the new database in (in most cases hosts prevent you from installing a .BAK file). I’ve said it before and I’ll say it again: migrating MySQL DBs is like shipping a boat full of containers from china to los angeles. Migrating a MS SQL database is like dealing with refugees fleeing a war zone. Can someone please write a script that automates the hell i am now going through? Parts of that script exists, but there is still no end-to-end solution that works with large databases! Come on, people, it’s 2010. I guess that’s why all the DB work that matters is happening on MySQL!
on February 6, 2010 at 10:44 pm | Reply Pinal Dave

Hello Judas,

Please check the article:
http://mssqlonline.blogspot.com/2009/07/sql-server-2008-copy-database-with-data.html

If you not want to include data in scrip the set the “Script Data” option to Flase.

Regards,
Pinal Dave
on February 27, 2010 at 11:16 pm | Reply Hari Prasad M

I did not get the exact answer in the sql authority articles. My staight question is, can we generate the script with data of table in SQL server 2005. I read most of the articles in SQL athority but every article is describing about the SQL 2008.

I tried to generate the script in 2005 but I am unable to see the script data in 2005. I am not sure whether this is available in 2005 or not? Could you please clarify my doubt.
on March 12, 2010 at 11:14 am | Reply kaliass

i want restore the database by script with data….
bcoz i want change my db 2000 to 2005.
on March 24, 2010 at 3:43 pm | Reply Yoshi

Hi Pinal,

Thank for the tool..i have an urgent questions
1. the tool creates the contents of the db without the db, i need the db first.

2. i dont need the data in the script.

please help..btw nice to meet you
- on December 15, 2011 at 8:22 am | Reply Kehao
  
  hey,
  
  on the top of the script type
  
  USE [your database name]
  
  That will import the files over…
on April 14, 2010 at 1:42 am | Reply Varun

Nice one.

How can I get Schema and Data(Only for Selected Tables) using Publishing Wizard.

My task is to script of whole Database where I can, but also need data from selected tables(Not needed from all).

Is it a way I can do it.
on September 14, 2010 at 6:31 pm | Reply Sid

Thanx. This really helped :)
on September 27, 2010 at 12:42 pm | Reply khurram saddique

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
on November 24, 2010 at 7:20 pm | Reply Mark

Hi Pinal,

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

Thank you,

Mark
on December 29, 2010 at 12:29 am | Reply Harie

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.
on December 29, 2010 at 10:25 am | Reply k sankara raman

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]
on January 19, 2011 at 3:02 pm | Reply Kira K

How to generate script of of a database with data in tables
- on January 21, 2011 at 4:22 pm | Reply madhivanan
  
  If you use version 2008, there is an option. Otherwise you need to use import/export wizard to export data
on April 12, 2011 at 11:51 am | Reply Kumaresh Chandra Baruri

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

Thanks
on June 28, 2011 at 8:28 pm | Reply martin

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.
on September 23, 2011 at 10:11 pm | Reply jim

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.
- on November 8, 2011 at 6:15 am | Reply Bill
  
  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.
on January 12, 2012 at 1:03 pm | Reply blahblah1233445

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
on February 1, 2012 at 9:21 pm | Reply Pradhan

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
on February 20, 2012 at 11:57 am | Reply Seeni

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.
on February 28, 2012 at 3:52 am | Reply akosirenee

hi. how can this generated script be use to convert it to mysql?
can anyone help me. thnaks
- on March 21, 2012 at 1:47 pm | Reply madhivanan
  
  You need to manually convert it. Becuase some of the datatype names are different in mysql
on March 16, 2012 at 1:45 pm | Reply Jadav Vishal

this tool is given by microsoft, but pinal u give the idea how to use it. great. thank for the help..
on March 16, 2012 at 3:11 pm | Reply Himmat Jadhav

hi pinal ,
how to remove timout expire error from sql server 2000
pls give me solution
- on March 20, 2012 at 2:23 pm | Reply madhivanan
  
  When do you get this error?
on March 27, 2012 at 5:27 pm | Reply tarun

whats for Remore server
on April 13, 2012 at 12:48 pm | Reply Sudarshan

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.
on May 26, 2012 at 12:40 am | Reply Vlad

Really thank for this help
on June 1, 2012 at 11:22 am | Reply harika

Hi, Not able to find script data option in sql server 2008, please help
on September 5, 2012 at 8:09 pm | Reply aakash parwani

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.
on October 31, 2012 at 7:02 am | Reply SQL SERVER – Copy Data from One Table to Another Table – SQL in Sixty Seconds #031 – Video « SQL Server Journey with SQL Authority

[...] SQL SERVER – 2005 – Generate Script with Data from Database – Database Publishing Wizard [...]
on November 3, 2012 at 9:16 pm | Reply Aman Mahal

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.
on November 17, 2012 at 7:01 am | Reply SQL SERVER – Weekly Series – Memory Lane – #003 « SQL Server Journey with SQL Authority

[...] Generate Script with Data from Database – Database Publishing Wizard Generating schema script with data is one of the most frequently performed tasks among SQL Server Data Professionals. There are many ways to do the same. In the above article I demonstrated that how we can use the Database Publishing Wizard to accomplish the same. It was new to me at that time but I have not seen much of the adoption of the same still in the industry. [...]
on March 22, 2013 at 1:49 pm | Reply sumeyye

thank you