How to Generate Scripts in SQL Server With Data

What does it mean to generate scripts with data in SQL Server?

In SQL Server, creating SQL scripts with data means that you make a single SQL file that contains both the database's definition and the records inside it. The script includes all objects (tables, views, indexes, constraints) followed by INSERT statements for the data itself. When executed, it rebuilds the database precisely as it exists at the moment of scripting.

It is different from the default "schema only" option in SSMS, which captures the structure but leaves the tables empty. Schema-only scripts are lighter and faster to produce, but they require separate data loading steps to create a working copy.

Schema vs schema + data scripts

Bottom line: Schema-only scripts give you the blueprint; schema + data scripts give you the fully furnished building.

When do you need scripts with data?

Writing SQL scripts that include both schema and data is the right choice if you need a complete and portable snapshot of your database. Typical scenarios include:

• Migrating databases in a single deployable file that contains both structure and data.
• Capturing reference and seed data alongside the schema for version control.
• Creating tests or staging environments with realistic production data.
• Sharing a plug-and-play database for demos, training, or collaboration.

However, for large transactional systems, this method can be impractical due to file size and execution time. In those cases, backups, BACPAC exports, or bulk copy tools are more efficient.

Before outlining how to generate these scripts, let's compare the capabilities of the two primary tools available for this process.

SSMS vs dbForge Studio for SQL Server

While both SSMS and dbForge Studio for SQL Server can create SQL scripts with schema and data, their capabilities differ significantly. The table below highlights the key differences to help you choose the right tool for your workflow.

With the differences in mind, let's look at how to generate schema and data scripts in each tool.

Using dbForge Studio for SQL Server to generate scripts with data

dbForge Studio for SQL Server condenses SQL Server scripting into a faster and more controlled process. Compared to SSMS, it consolidates schema and data scripting, filtering, formatting, and export options into a single, unified workflow, eliminating the need for multiple wizard screens. Additionally, such options as built-in row-level export filtering and preview and edit before saving allow us to avoid re-runs for small changes.

Now, let us explore how to generate scripts with data using dbForge Studio for SQL Server, step by step.

1. Launch the Generate Scripts tool. Choose Tasks from the Database menu, and select Generate Scripts. Alternatively, right-click the necessary database and select the same option from the context menu.

2. Check the general script generation options. You can specify the necessary SQL Server connection, the database, the path to store the script, define the output file name, and configure other options.

3. Optional: Load an existing script generation project file. If you already have a backup file, click Open to select it. Otherwise, skip this step.

4. Choose the content to script. On the Script content page, select both Structure and Data to include the database schema and all records. You can also include or exclude specific tables, views, and other objects.

5. Configure script options. On the Options page, define settings such as including indexes, triggers, constraints, collation, and extended properties.

6. Set error handling preferences. On the Error handling page, choose how the application should process errors and whether it should generate logs. Click Generate to start the process.

When the process completes, you can open the generated file immediately by selecting Open script and clicking Finish.

Next, let's walk through the same process in SSMS so you can see how it compares step by step.

How to generate a script with data using SSMS

SQL Server Management Studio (SSMS) includes a built-in Generate Scripts wizard that can output both schema and data. While it's not as flexible as third-party tools, it's reliable for small to medium datasets and quick exports. Let's explore how to use the SQL Server Management Studio's generate scripts feature.

Step 1: Open SSMS and launch the Generate Scripts wizard

• In Object Explorer, expand the server node and locate the target database.
• Right-click the database name, hover over Tasks, and select Generate Scripts…
• It opens the Generate and Publish Scripts wizard.

Step 2: Select objects to script

In the Choose Objects step, decide whether to:

• Script the entire database and all database objects - captures all tables, views, stored procedures, and functions.
• Select specific database objects - pick only the tables, views, or procedures you need.

Step 3: Set scripting options

In the Set Scripting Options page, choose how and where to save the generated script:

• Save as notebook - saves the script in a notebook format.
• Save as script file - outputs a .sql file for reuse, either as a single file or one file per object.
• Save to clipboard - copies the script for pasting directly into a query editor.
• Open in new query window - opens the script in SSMS for immediate execution or editing.

In SSMS, the Advanced Scripting Options dialog is the make-or-break step for generating scripts that contain both schema and data. By default, SSMS scripts only the schema, so you must explicitly change Types of data to script to Schema and Data.

• In the Set Scripting Options page of the Generate Scripts wizard, click Advanced.
• Scroll to the General section.
• Locate Types of data to script and change it from Schema only to Schema and data.

Without this change, the output will include only object definitions, no INSERT statements, regardless of the objects you selected earlier.

Other advanced options to consider

While you're in the Advanced Scripting Options dialog, it's worth adjusting other settings that can affect the completeness and portability of your script:

• Script DROP and CREATE: Useful when you need the script to recreate objects from scratch, replacing existing ones.
• Include Collation: Preserves collation settings for consistent text handling across environments.
• Script Indexes, Triggers, and Constraints: Ensures you capture performance and integrity rules along with the data.

Tuning these options upfront saves time later and prevents missed dependencies or inconsistencies when you run the script in a new environment.

Click OK, then Next, review the summary, and click Finish to generate the script.

Import-Module SqlServer

# Define parameters
$server = "SERVERNAME\INSTANCE"
$database = "MyDatabase"
$outputFile = "C:\Scripts\MyDatabase_SchemaAndData.sql"

# Connect to SQL Server
$serverConnection = New-Object Microsoft.SqlServer.Management.Smo.Server $server
$db = $serverConnection.Databases[$database]

# Set scripting options
$options = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$options.ScriptData = $true
$options.ScriptSchema = $true
$options.IncludeIfNotExists = $true
$options.ToFileOnly = $true
$options.FileName = $outputFile

# Script all tables
foreach ($table in $db.Tables) {
    $table.Script($options)
}

ssms.exe -S SERVERNAME\INSTANCE -d MyDatabase -i "C:\Scripts\MyDatabase_SchemaAndData.sql"