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.



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



3. In above step you can see our instance is connected. Now click on the Database for 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.



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



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:



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:
   
   

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.



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.



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.

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:



13. Click on scroll down for 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.



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



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



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.



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:



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.
   
   
   
   
2. Employee table.
   
   

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



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



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





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.



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



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



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!