Saturday, October 30, 2010

Generate database scripts in SQL Server

SQL Server gives a very useful tool ‘Database Publish Wizard’ that can be used to script and publish SQL Server database. For SQL Server 2005 installation, this tool is usually located at [Program Files]\Microsoft SQL Server\90\Tools\Publishing\SqlPubWiz.exe. This tool has both command line as well as GUI interface.

Here I am going to show some sample command line queries that can be used to generate database scripts. For the sake of example, let us assume that database name is “MyDatabase” which is hosted on SQL Server instance “SQLEXPRESS” on the local machine.
Following command will give you script for schema (not data) of the database. The script is output to a text file C:\Script.txt.

SqlPubWiz.exe script -S .\SQLEXPRESS -d MyDatabase -schemaonly -f C:\Script.txt

Option “-S” specifies the SQL Server instance. Options “-d” specifies the database to script. Options “-schemaonly” specifies that we want to script only schema. Options “-f” specifies the output file.
SqlPubWiz by default works in windows authentication mode. So if your Server does not allow for windows authentication, you will also need to specify user name and password with “-U” and “-P” option respectively.

In the same way as above command, following command will generate script for all the data of the database. It will not contain any schema script.

SqlPubWiz.exe script -S .\SQLEXPRESS -d MyDatabase -dataonly -f C:\Script.txt

We can even use Management Studio to generate database scripts (Right click database, select Task and then select Generate Scripts). However, there is no options in management studio to generate “dataonly” scripts in which case this tool comes handy.

17 comments:

Deepika said...

Thanks for such nice information with excellent explanation. Thanks and keep on updating.
Austere Technologies | TOP clod solution company | NO.1 IT services company

Deepika said...

REALLY VERY EXCELLENT INFORMATION. I AM VERY GLAD TO SEE YOUR BLOG FOR THIS INFORMATION. THANKS FOR SHARING. KEEP UPDATING.

NO.1 API DEVELOPMENT SERVICES | MASSIL TECHNOLOGIES

Unknown said...

nice blog.
Best Digital Transformation Services | Austere Technologies

Austere said...

Very interesting information. Thanks for sharing such nice article.

Best Mobility Services | Austere Technologies

Deepika said...

Excellent informative article. Thanks for sharing such nice article, keep on updating such good articles.

Best IT Security Services | Austere Technologies

Deepika said...

Nice blog with excellent information. Thank you, keep sharing.

chartered accountant course in Hyderabad | Avinash college of commerce

Deepika said...

Great article, really very helpful content you made. Thank you, keep sharing.

Best Degree Colleges Hyderabad | Avinash College of Commerce

sandy said...

Hi Thanks for the nice information its very useful to read your blog. We provide best Find All Isfs Courses

Deepika said...

Excellent informative blog, keep for sharing.

Best System Integration services | Massil Technologies

Unknown said...

nice post. very informative. thank you

Deepika said...

Excellent informative blog, keep sharing.CA institute in hyderabad | Avinash College of Commerce

akash said...

Great post.. Thank you
Digital marketing course in Chennai

Keerthana said...

Well Done Works!!!Keep Going with this
python training in chennai | python training in annanagar | python training in omr | python training in porur | python training in tambaram | python training in velachery

bill.wood said...

Data analysts and data scientists are the most sought after by companies like LinkedIn, Facebook, Groupon and Amazon. These companies have to deal with enormous amount of raw data and seek the high-tech experts to simplify the job for them. machine learning course in hyderabad

lakshmik7410 said...

An interesting blog page. Thanks for sharing.

data science training in chennai

ccna training in chennai

iot training in chennai

cyber security training in chennai
ethical hacking training in chennai

Unknown said...

Thanks for sharing information to our knowledge, it helps me plenty keep sharing…

Big Data Hadoop Online Training
Online Training For Big Data

Samia Beel said...

Kon-Boot 3.3 crack may be a programme for Window frames versions of windows that on the QT escapes the verification system. Kon Boot Free Download Crack