In this article, we will explore various ways for scripting SQL Server database objects.
DBAs and developers manage environments for a SQL database. While managing multiple copies, sometimes we require the following tasks.
- 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
- 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.
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
In the next step, we get options to script an entire database or select specific objects from the following list.
- Stored procedure
- User-defined functions
- User-defined data types
- DDL triggers
- XML schema collections
- Full-text catalogs
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
We can still do a lot with this generate script wizard. Click on Advanced. It opens 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 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 the database engine edition: We can choose the required database engine edition, and it scripts features compatible with that 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
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
Table/ View Options: it provides various configurations for script tables and views such as script constraints, indexes, primary and foreign keys
Once you have configured the required options, click next for review of the 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.
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.
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 188.8.131.52
- 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
On the next page, validate the configurations.
Click next, and it builds a DACPAC package for the source database
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.
It launches the following wizard along with the introduction and 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.
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.
Specify a new SQL database name, and a red dot icon disappears.
Review the deployment configuration.
Click Next, and it starts the DACPAC deployment.
Once the deployment is finished, refresh databases in the Object Explorer in SSMS.
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.
- SUBSTRING, PATINDEX and CHARINDEX string functions in SQL queries - March 1, 2021
- Deploy Azure Data Lake Analytics database using the U-SQL scripts - February 23, 2021
- Join database tables using U-SQL scripts for Azure Data Lake Analytics - February 17, 2021