Introduction
DBAs and developers manage the database environment. When managing databases in different environments, a DBA needs multiple copies. For this, DBA uses various approaches like database backup and restore method and object scripting using generate script option. Sometimes, DBAs and developers don't always need the data and instead need to export and import database objects into the target database.
I know you have a question about the use of generating database object script(s). The answer is, sometimes DBAs have to move the database(s) or database object(s) to another version of SQL Server. And, normally they use the backup and restore approach. But, sometimes, the backup (.bak) file will not be restored due to different versions of SQL Server.
In this tutorial, I am going to explain various methods to generate scripts for various database objects in SQL Server 2019. This detailed article will cover the following topics as follows,
- Introduction
- Prerequisites
- Generate Scripts Wizard
- Shortcut Method (Without Data)
- Conclusion
Prerequisites
In order to generate the script(s), make sure that you must have,
- SQL Server Management Studio (SSMS)
- Connect to a server that is running SQL Server.
- A database
(Here, I am using a dummy database for the demonstration purpose which you can download from here).
Method 1 - Using Generate Scripts Wizard
The Generate Script Wizard is generally used to create scripts for transferring databases between instances of the SQL Server Database Engine. You can generate scripts for an entire database, or limit it to specific database objects (viz, database, table, stored procedure, and many more) on an instance of the Database Engine. You can script both the schema and the data by using the "Generate Scripts" method available in SQL Server Management Studio (SSMS). However, scripting tables with data is not ideal with large databases. The generated scripts can be run on another instance of the Database Engine or any other server.
Follow the given below instructions to generate scripts for all database objects.
Step 1
First of all, connect to a server that's running SQL Server.
Step 2
In Object Explorer, expand the "Databases" node.
Step 3
Right-click on the "AdventureWorks2019" Database and select Tasks > Generate Scripts.

Now, complete the "Generate Scripts" wizard by following the below steps,
- Introduction Page
- Choose Objects Page
- Set Scripting Options Page
- Advanced Scripting Option Window
- Summary Page
- Save Scripts Page
Introduction Page
"Generate Scripts" Window with "Introduction" Page will appear on the screen with a detailed description and steps for generating and publishing scripts for database objects. Click the "Next" button to begin the script generation process.

Choose Objects Page
Now, the "Choose Objects" page will open with various options for selecting database object(s). Use this page to choose which database object(s) you want to script.
A) Script entire database and all database objects option
Select this option to generate scripts for the entire database objects (tables, views, stored procedures, functions, triggers, and many more), including a script for the database itself.

B) Select specific database objects option
This option will empower you to generate scripts only for specific database object(s) in the database you choose.

Examples
The examples in this section demonstrate how to script particular database object(s) using Generate Scripts Wizard. Let's see.
1) Scripts Table
Expand the "Tables" option and select the table(s) which you want to script. Now, click the "Next" button to proceed.

2) Scripts Stored Procedures
Expand the "Stored Procedures" option and select the Stored Procedure(s) which you want to script. Now, click the "Next" button to proceed.

Set Scripting Options Page
The "Set Scripting Options" Page will open with various additional advanced options. This page enables you to save scripts to a location of your choice that you can then run scripts against in an instance of the database engine or against a SQL database. Now, specify how the scripts should be saved.
a) Select the "Save as Script file" option. And, choose, "One script file per object" option to generate a separate file for each database object(s).












Gurpreet AroraPosted Jun 26, 2021, 6:54 PM
Great Explanation