Create script for all objects in database with data

You can create T-SQL scripts for database, objects in database and insert script for data in tables by using the Generate Scripts Wizard.

These scripts can be used to create database in secondary site with and with out data.

I this blog I will explain how to generate T-SQL script to create database, database objects and data.

Right click the database which you want to script—>Select tasks—>Generate scripts.

clip_image001

 Generate and Publish Scripts wizard screen appears.click Next to continue.

clip_image001[4]

In the Choose Objects wizard screen Select “Script entire database and all database objects” option if want to script all the objects in database or select specified objects which you would like to script and click Next to continue.

clip_image001[6]

In the Set Scripting Options wizard screen.Select the output type as “Save scripts to a specific location” and specify the path where you would like to save the database script file .Click the Advanced button  for specifying the schema and data scripting option.

clip_image001[8]

In Advanced Scripting Options screen; choose the option Schema and data for the Types of data to script option if you want to generate insert scripts for rows in tables  and click OK to save the changes and to return to Set Scripting Options wizard screen.

a) Data only – If this option is selected, it will only script out data within the tables
b) Schema and data – If this option is selected, it will script out Schema as well as the data within the selected objects
c) Schema only – If this option is selected, it will script out the Schema only

clip_image001[10]

In the Summary wizard screen; you will be able to see a quick summary of all the options which you have selected so far. Click Next to confirm and generate the scripts.

clip_image001[12]

In Save or Publish Scripts wizard screen; you will be able to see a Success or Failure message against each object for which the script was requested to be generated. Finally, click Finish to close the wizard.

clip_image001[14]

Once the script is generated you can view it like any other SQL Script. 

clip_image001[16]

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s