Generate Database Script In SQL Server 2012

In this article we are learning how to generate database script in SQL Server 2012.

We can see generated database script with various option like Schema only, Data only and Schema and Data.

The following are the steps to generate database script in SQL Server 2012:

1. Open Microsoft SQL Server Management Studio 2012.

Open Microsoft SQL Server Management Studio 2012

2. Connect with the database instance, like above click connect it will show the following screen.

Connect with the database instance

3. In above step you can see our instance is connected. Now click on the Database for selecting database to generate script.

selecting database to generate script

4. In above screen you can see I selected database name ‘ScriptDatabase’. In that database I have two tables ‘Department’ and ‘Employee’ and we are using this database to generate script.

5. Right click on selected database, then select Task.

6. Click on Task next arrow and select Generate Scripts. See the following image.

genetare script

7. Next it will open one popup dialog like the following:

Click next

8. In the above screen you can see there is a wizard control to generate script. It also gives steps on how to generate script. Using the wizard click Next and you can see the following screen:

select table

9. It will show two option radio button for selecting the database objects to script.

  1. Script entire database and all database objects.
  2. Select specific database objects.

    Select specific database scripts from the two options as in the following screenshot:

    database script generate

Here also you can select all the objects like the first option and as in the following screen you can see for selecting a single object in database or table.

object in database or table

In the above screen you can see I selected only ‘Department’. In this article I am going to use the whole database.

10. Click on Next button and it will show the next step.

Click on Next button image

In the above screen you can see it will ask for specifying how script should be saved or published with two radio button options.

11. Here we are selecting first option because we are saving the script to particular location. There is also various options such as,

  1. Save to File.
  2. Save to Clipboard.
  3. Save to new query window.

new query window

In the above screenshot you can see I provided location to save the script to a specific location. Here are the two options for Files to Generate:

  1. Single File
  2. Single file per object

The option name specifies the functionality when we select single file that means save all objects in a single file. The second is single file per object that means it creates a new file for every object. Click ‘Advanced’ button.

12. It will popup new dialog box for selecting Advanced Script Options like the following:

selecting Advanced Script Options

13. Click on scroll down for selecting our option for scripting options.

selecting our option for scripting options

In the above screenshot you can see various options are available under the ‘Types of data to script’. The options are as follows:

  • Data only.
  • Schema and Data.
  • Schema only.

By default schema only option is selected. Click on OK button.

14. Now Advanced scripting option is done now click on Next button. It will show the selected database name and objects, location of script file we are saving and we selected advanced scripting option here by default is selected Schema only.

selected Schema

In the following screenshot you can see default selected values in general.

default selected values in general

15. Click on Next button it will show screen like the following:

Click on Next button

You can see the above screen that script is generated result is success! Great congratulations you generated the database script successfully. Click on Finish.

16. Now, open the folder location for generated database script with Schema only option.

generated database script with Schema only option

The above screen shot you can see the generated script with name ‘Script_only.sql’.

17. Double click on generated script it will open MS SQL Server 2012, see the following screen:

generated script it will open MS SQL Server 2012

In the above screen shot you can see we selected database name and tables.

For generating script with other options follow the steps [6-13]. Now I’m going to select the second option Schema and Data. The following screen shows data in my database ‘ScriptDatabase’.

  1. Department table.

    Department table

  2. Employee table.

    Employee table

Now, I’m generating script with advanced script open of ‘Types of data to script’, see the following screen:

Types of data to script

In next follow the steps [14-17], you can see my script is generated with Schema and Data option.

script is generated with Schema

Open the script it will open in MS SQL Server, you can see the following screenshot:

script it will open in MS SQL Server

query

The above two screen shows the generated database Schema and Data.

For generating script with other options follow the steps [6-13]. Now I’m going to selecting third option Data only.

selecting third option Data

In next follow the steps [14-17], you can see my script is generated with Data only option.

script is generated with Data

Open the script it will open in MS SQL Server, you can see following screen shot:

Open the script

The above screenshot shows the data in generated script. You successfully generated database script with advanced script options – Schema only, Schema and Data, Data only.

Summary

I hope that beginners as well as students understand how to generate the database script with advanced scripting options. If you have any suggestion regarding this article, then please contact me.

Thanks for reading. Learn it! Share it!