Menu

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.

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

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.

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.

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 tablesb) Schema and data – If this option is selected, it will script out Schema as well as the data within the selected objectsc) Schema only – If this option is selected, it will script out the Schema only

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.

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.

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