Rajendra Gupta
Advanced Scripting Options.

Methods to script SQL Server database objects

February 14, 2020 by

In this article, we will explore various ways for scripting SQL Server database objects.

Introduction

DBAs and developers manage environments for a SQL database. While managing multiple copies, sometimes we require the following tasks.

  1. Database refresh from a production environment to the lower environments. It requires copying all database objects such as database schema, tables, stored procedures, functions, indexes. We use multiple approaches such as database backup restore, object scripts, BACPAC method depending upon the requirement
  2. Sometimes, we do not require data and have the requirement to export and import all objects in a destination database

In this article, we will explore different ways to export database objects without data.

Generate Scripts Wizard for SQL Database

We can use Generate Scripts wizard for scripts of all database objects or specific objects. It provides various configuration options to choose from.

Right-click on the source SQL database for which you want to generate script and launch Generate scripts wizard as shown below.

Generate Scripts Wizard

It launches generate scripts wizard with a brief introduction and high-level steps.

  • Select database objects
  • Specify scripting options
  • Review your selections
  • Generate script and save them

Generate Scripts Wizard for database objects

In the next step, we get options to script an entire database or select specific objects from the following list.

  • Tables
  • Views
  • Stored procedure
  • User-defined functions
  • User-defined data types
  • DDL triggers
  • XML schema collections
  • Schemas
  • Full-text catalogs

Select objects

Let’s select the first option to script the entire database and click Next. Here, you get different options to save the script.

  • Script to file
  • Script to a new query window
  • Script to clipboard

Advanced configurations

We can still do a lot with this generate script wizard. Click on Advanced. It opens Advanced Scripting Options.

Advanced  Scripting Options.

In this window, you can do the required changes for generating a script for the selected objects. We cannot cover all configurations here. However, I will highlight a few useful changes. Few configurations are true, by default.

  • Script Drop and Create: Default configuration is to generate script with a Create statement. We can change it to Script Drop, Script Drop and create as well

    Script Drop and Create

  • Script for Server version: We might require a script for a different version of SQL Server. We can use SQL Server versions starting from SQL Server 2005. You should generate a script for an appropriate version as few commands and syntaxes do not work in a different version of SQL Server

    Script for Server version

  • Script for the database engine edition: We can choose the required database engine edition, and it scripts features compatible with that edition

    Script for the database engine edition

  • Script logins, Object-level permissions, Owner, Statistics: By default, it does not script any of the logins, object-level permissions, database owner and statistics. We can change the corresponding value to true so that it generates those scripts as well

    Script logins, Object-level permissions, Owner, Statistics

  • Type of data to the script: By default, it generates a script for the selected objects. It does not script data. We can modify this configuration and choose from Data only, Schema and data and schema only values

    Type of data to the script

  • Table/ View Options: it provides various configurations for script tables and views such as script constraints, indexes, primary and foreign keys

    Table/ View Options

Once you have configured the required options, click next for review of the selections.

Review your selections

You can go back and change any settings. If no changes required, click Next and you can see the status for each object script. Once you finish it, you can see the script per the configured option.

Preparing scripts

DACPAC export and Import

In the article Importing a BACPAC file for a SQL database using SSMS, we explored the use of a BACPAC package file for export tables data and import them into a new database. Later, we use it for data refresh from the source to the destination database.

Data-Tier Application Package (DACPAC) creates a logical package consisting schema of database objects. We can exchange these packages between developers, DBAs for exporting database schema without worrying about the data. We can use SQL Server Data tools and SQL Server Management Studio for this.

Right-click on the desired database in SSMS and you can see Data-tier application options.

Data-tier application SSMS

In the same previous article (link mentioned above), we explored the Export data-tier application for a BACPAC package. We use the Extract Data-tier application for a DACPAC package export.

First, it launches an introduction page specifying high-level steps.

  • Set the DAC properties
  • Review object dependencies and validation results
  • Build the DAC package

On the next page, specify the following values.

  • Application name
  • Version: We can create different versions of a DACPAC package. By default, it uses first DACPAC version 1.0.0.0
  • Description: It is an optional field. It is always good to give a short description specifying the purpose of this export. We can skip the description as of now
  • Save to DACPAC package file: Specify the directory for storing this package file. Y default, it stores the file inside the SSMS directory
  • Overwrite existing file: If the DACPAC file with the same name already exists, we can choose this option to overwrite an existing file

DACPAC export and Import

On the next page, validate the configurations.

Validation & Summary

Click next, and it builds a DACPAC package for the source database builds a DACPAC package

Deploy data-tier applications

Once we have DACPAC export, we can import it in the desired instance as a new database. Right-click on SQL database node and click on Deploy data-tier applications.

Deploy data-tier applications

It launches the following wizard along with the introduction and high-level steps for DACPAC import.

high-level steps for DACPAC import

  • Select the DAC package
  • Configure the deployment
  • Review the selection
  • Deploy a DAC package

In the next step, select the DAC package we exported earlier.

select the DAC package

Click Next and specify the database name. By default, it takes the source database name. In the following screenshot, you can see a red dot that shows that this database [AdventureWorks] already exists in this instance. Deployed DAC and database

Specify a new SQL database name, and a red dot icon disappears.

Specify a new database name

Review the deployment configuration.

Review the deployment configuration

Click Next, and it starts the DACPAC deployment.

DACPAC deployment

Once the deployment is finished, refresh databases in the Object Explorer in SSMS.

refresh SQL databases

Conclusion

In this article, we explored Generate Scripts wizard and DACPAC package for export/import schema for SQL database objects. You should review the approaches and use the best fit for you.

Rajendra Gupta
685 Views