---

Search posts under SQL Server:

---

Generate SQL script of whole database in SQL Server

How to generate SQL script of the whole database in SQL Server?

To generate script of the whole database structure so that the same database structure can be created on another server with no data, we can right click on Database and select Tasks > Generate Scripts…

Select Next > on below dialog box.

In the following dialog box, select whether we want to script all database objects like Database tables, stored procedures, views or want to be very specific on which database object we want to generate script for.

To be specific, select 2nd radio button and select appropriate Table, Views, Stored Procedures

Click Next > and select proper Output type.

To save the file on the current system, select first radio button from the Output Type and then write the file name where we want to save the generated script file.

To save the generated in the Clipboard select “Save to Clipboard” radio button and to save the generated script in the new query window select “Save to new query window” radio button.

Press Next >

That will show the status of the script generated.

Note that by default, the Generate Scripts…. do not generate the script to insert the existing data from the source database. It only creates script to create the selected objects such as Database tables, stored procedures etc.

To generate the scripts of database data along with schema (structure) of the database, we need to click on Advance button on the Set Script Options dialog box (4^th image from top) and select "Types of data to script" option under General heading from "Advance Scripting Options" dialog box. This dialog box, gives many other options that will be taken care while generating the SQL scirpt.