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.

22 comments:

Inigo joseph said...

I believe there are many more pleasurable opportunities ahead for individuals that looked at your site.
rprogramming training in bangalore

Blogger Backlink profile 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

massil said...

Very good informative article. Thanks for sharing such nice article, keep on up dating such good articles.

NO.1 APP DEVELOPMENT SERVICES | MASSIL TECHNOLOGIES

masi 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

ram chinna said...

nice blog.
Best Digital Transformation Services | Austere Technologies

AUSTERO said...

Very interesting information. Thanks for sharing such nice article.

Best Mobility Services | Austere Technologies

AUSTERO said...

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

Best IT Security Services | Austere Technologies

desh said...

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

For your Best career in commerce | Avinash College Of Commerce

austere said...

Wow...What an excellent informative blog, really helpful. Thank you.

Software Testing | Austere Technology

satheesh sutharapu said...

Wow...What an excellent informative blog, really helpful. Thank you.

chartered accountant | Avinash college of commerce

avinash said...

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

chartered accountant course in Hyderabad | Avinash college of commerce

avinash said...

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

Best Degree Colleges Hyderabad | Avinash College of Commerce

Avinash said...

Very good informative blog, keep sharing.

Best junior college in Hyderabad | Avinash College of commerce

Avinash said...

Hi Thanks for the nice information its very useful to read your blog. We provide best Block Chain Services

sandy said...

Thank you for sharing this valuable information. But get out of this busy life and find some peace with a beautiful trip book best Andaman honeymoon packages

sandy said...

Thank you for sharing this valuable information. But get out this busy life and find some peace with a beautiful trip. book ANDAMAN HOLIDAY PACKAGES @ 35999

sandy said...

Thank you for sharing this valuable information. But get out this busy life and find some peace with a beautiful trip. book CHEAP ANDAMAN PACKAGES @9999

sandy said...

Thank you for sharing this valuable information. But get out this busy life and find some peace with a beautiful trip. book Andaman Tourism

sandy said...

Thank you for sharing this valuable information. But get out this busy life and find some peace with a beautiful trip. book Best Travel Agency In India

Avinash said...

Good information.

B.com Integrated Courses | 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

sandy said...

Hi Thanks for the nice information its very useful to read your blog. We provide best Massil Technologies