Export-DbaScript

Want to see the source code for this command? Check out Export-DbaScript on GitHub.
Want to see the Bill Of Health for this command? Check out Export-DbaScript.

Synopsis

Generates T-SQL CREATE scripts from SQL Server Management Objects for migration and deployment

Description

Takes any SQL Server Management Object from dbatools commands and converts it into executable T-SQL CREATE scripts using SMO scripting. This lets you script out database objects like tables, jobs, logins, stored procedures, and more for migration between environments or backup purposes. The function handles proper formatting with batch separators and supports custom scripting options to control what gets included in the output. Perfect for creating deployment scripts or documenting your SQL Server configurations without manual scripting.

Syntax

Export-DbaScript
    [-InputObject] <Object[]>
    [[-ScriptingOptionsObject] <ScriptingOptions>]
    [[-Path] <String>]
    [[-FilePath] <String>]
    [[-Encoding] <String>]
    [[-BatchSeparator] <String>]
    [-NoPrefix]
    [-Passthru]
    [-NoClobber]
    [-Append]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Get-DbaAgentJob -SqlInstance sql2016 | Export-DbaScript

Exports all jobs on the SQL Server sql2016 instance using a trusted connection - automatically determines filename based on the Path.DbatoolsExport configuration setting, current time and server name.

Example: 2

PS C:\> Get-DbaAgentJob -SqlInstance sql2016 | Export-DbaScript -FilePath C:\temp\export.sql -Append

Exports all jobs on the SQL Server sql2016 instance using a trusted connection - Will append the output to the file C:\temp\export.sql if it already exists
Inclusion of Batch Separator in script depends on the configuration s not include Batch Separator and will not compile

Example: 3

PS C:\> Get-DbaDbTable -SqlInstance sql2016 -Database MyDatabase -Table 'dbo.Table1', 'dbo.Table2' -SqlCredential sqladmin | Export-DbaScript -FilePath C:\temp\export.sql

Exports only script for 'dbo.Table1' and 'dbo.Table2' in MyDatabase to C:temp\export.sql and uses the SQL login "sqladmin" to login to sql2016

Example: 4

PS C:\> Get-DbaAgentJob -SqlInstance sql2016 -Job syspolicy_purge_history, 'Hourly Log Backups' -SqlCredential sqladmin | Export-DbaScript -FilePath C:\temp\export.sql -NoPrefix

Exports only syspolicy_purge_history and 'Hourly Log Backups' to C:temp\export.sql and uses the SQL login "sqladmin" to login to sql2016
Suppress the output of a Prefix

Example: 5

PS C:\> $options = New-DbaScriptingOption
PS C:\> $options.ScriptSchema = $true
PS C:\> $options.IncludeDatabaseContext  = $true
PS C:\> $options.IncludeHeaders = $false
PS C:\> $Options.NoCommandTerminator = $false
PS C:\> $Options.ScriptBatchTerminator = $true
PS C:\> $Options.AnsiFile = $true
PS C:\> Get-DbaAgentJob -SqlInstance sql2016 -Job syspolicy_purge_history, 'Hourly Log Backups' -SqlCredential sqladmin | Export-DbaScript -FilePath C:\temp\export.sql -ScriptingOptionsObject $options

Exports only syspolicy_purge_history and 'Hourly Log Backups' to C:temp\export.sql and uses the SQL login "sqladmin" to login to sql2016
Uses Scripting options to ensure Batch Terminator is set

Example: 6

PS C:\> Get-DbaAgentJob -SqlInstance sql2014 | Export-DbaScript -Passthru | ForEach-Object { $_.Replace('sql2014','sql2016') } | Set-Content -Path C:\temp\export.sql

Exports jobs and replaces all instances of the servername "sql2014" with "sql2016" then writes to C:\temp\export.sql

Example: 7

PS C:\> $options = New-DbaScriptingOption
PS C:\> $options.ScriptSchema = $true
PS C:\> $options.IncludeDatabaseContext  = $true
PS C:\> $options.IncludeHeaders = $false
PS C:\> $Options.NoCommandTerminator = $false
PS C:\> $Options.ScriptBatchTerminator = $true
PS C:\> $Options.AnsiFile = $true
PS C:\> $Databases = Get-DbaDatabase -SqlInstance sql2016 -ExcludeDatabase master, model, msdb, tempdb
PS C:\> foreach ($db in $Databases) {
>>        Export-DbaScript -InputObject $db -FilePath C:\temp\export.sql -Append -Encoding UTF8 -ScriptingOptionsObject $options -NoPrefix
>> }

Exports Script for each database on sql2016 excluding system databases
Uses Scripting options to ensure Batch Terminator is set
Will append the output to the file C:\temp\export.sql if it already exists

Required Parameters

-InputObject

Accepts any SQL Server Management Object (SMO) from dbatools commands like Get-DbaLogin, Get-DbaAgentJob, or Get-DbaDatabase.
Use this when you need to generate CREATE scripts for specific database objects, jobs, logins, or other SQL Server components.
The object type determines what kind of T-SQL script will be generated.

Optional Parameters

-ScriptingOptionsObject

Accepts a customized SMO ScriptingOptions object created with New-DbaScriptingOption to control script generation.
Use this when you need specific formatting like including schema context, headers, or data along with object definitions.
Settings in this object override other Export-DbaScript parameters like BatchSeparator.

-Path

Sets the directory where script files will be saved when FilePath is not specified.
Defaults to the configured dbatools export directory from your Path.DbatoolsExport setting.
Use this when you want scripts saved to a standard location with auto-generated filenames.

-FilePath

Sets the complete file path and name for the output script file.
Use this when you need the script saved to a specific location with a custom filename.
Overrides the Path parameter when specified.

-Encoding

Controls the character encoding used when writing script files to disk.
Default is UTF8 which handles international characters and is widely supported.
Use UTF8 for most scenarios, or ASCII if you need compatibility with older tools that don't support Unicode.

-BatchSeparator

Sets the batch terminator added between SQL statements in the output script.
Defaults to "GO" from your dbatools configuration, which is standard for SQL Server Management Studio.
Use a different separator if deploying to tools that require different batch terminators.

-NoPrefix

Suppresses the header comment block that normally identifies when and by whom the script was generated.
Use this when you need clean scripts without metadata comments for automated deployments.
The prefix normally includes timestamp, username, and dbatools version information.

-Passthru

Returns the generated T-SQL script as string output instead of writing to a file.
Use this when you need to capture the script in a variable for further processing or modification.
Commonly used in pipelines to transform scripts before saving.

-NoClobber

Prevents overwriting existing files, causing the command to fail if the target file already exists.
Use this as a safety measure when you want to ensure you don't accidentally replace existing scripts.
Combine with -Append if you want to add to existing files instead.

-Append

Adds the generated script to the end of an existing file instead of overwriting it.
Use this when building consolidated deployment scripts by combining multiple objects into one file.
Particularly useful for scripting multiple databases or object types into a single migration script.

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

-WhatIf

Shows what would happen if the command were to run. No actions are actually performed

-Confirm

Prompts you for confirmation before executing any changing operations within the command