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
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.
Pinal,
As it turns out it seems to be a part of sql server 2008 already. I was expecting a separate install!
Great Tool !
I was doing backup/restore everytime !!
Saved much time.
how to script table data in SQL Server 2005
Thanks, super tool!
Great Tool !
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
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 ?
thank you
it helped me
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”.
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.
Hi all,
Could anyone suggest me a stored procedure to generate a script for crud operation
Thanks for step by step solution
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!
Hello Judas,
Please check the article:
If you not want to include data in scrip the set the “Script Data” option to Flase.
Regards,
Pinal Dave
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.
i want restore the database by script with data….
bcoz i want change my db 2000 to 2005.
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
hey,
on the top of the script type
USE [your database name]
That will import the files over…
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.
Thanx. This really helped :)