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.
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://darkslategrey-bat-805937.hostingersite.com)
69 Comments. Leave new
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
why users are converted to roles during the scripting. How to overcome this limitation? Apart from that its a good tool
Try SQLScripter ) 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
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…..
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)
This worked perfect. Thanks for posting this.
how can we use this for database on remote machine..i.e using sql server authentications
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
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~
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
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
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
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!
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…
i have a scrilpt file.i want to generate the database.i.e.
tables ,stored procedures etc.
how can
i do it?
Hi All, How to Generate cyclic order using sq l server
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
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.
great tool..!
Thank you Pinal.
This tool solves my Problem.
Great Tool …….
Thanks Pinal :)