How do I script a SQL Server database with data?

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.

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.

• Tables
• Views
• Stored procedure
• User-defined functions
• User-defined data types
• DDL triggers
• XML schema collections
• Schemas
• 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 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

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.

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.

• Author
• Recent Posts

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at

View all posts by Rajendra Gupta

How do I create a script of a table with data in SQL Server?

How do I write a SQL database script?

How do I create a SQL Server database script automatically?

How do I create a script with table data in SQL Server 2012?