< Back
Export-DbaInstance
Post
NAME Export-DbaInstance
SYNOPSIS
Exports SQL Server *ALL* database restore scripts, logins, database mail profiles/accounts, credentials, SQL Agent
objects, linked servers,
Central Management Server objects, server configuration settings (sp_configure), user objects in systems databases,
system triggers and backup devices from one SQL Server to another.
For more granular control, please use one of the -Exclude parameters and use the other functions available within
the dbatools module.
SYNTAX
Export-DbaInstance [-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [[-SqlCredential]
<Pscredential>] [[-Credential] <Pscredential>] [[-Path] <String>] [-NoRecovery <Switch>] [-IncludeDbMasterKey
<Switch>] [[-Exclude] <System.String[]>] [[-BatchSeparator] <String>] [-Append <Switch>] [[-ScriptingOption]
<Microsoft.SqlServer.Management.Smo.ScriptingOptions>] [-NoPrefix <Switch>] [-ExcludePassword <Switch>]
[-EnableException <Switch>] [<CommonParameters>]
DESCRIPTION
Export-DbaInstance consolidates most of the export scripts in dbatools into one command.
This is useful when you're looking to Export entire instances. It less flexible than using the underlying
functions.
Think of it as an easy button. Unless an -Exclude is specified, it exports:
All database restore scripts.
All logins.
All database mail objects.
All credentials.
All objects within the Job Server (SQL Agent).
All linked servers.
All groups and servers within Central Management Server.
All SQL Server configuration objects (everything in sp_configure).
All user objects in system databases.
All system triggers.
All system backup devices.
All Audits.
All Endpoints.
All Extended Events.
All Policy Management objects.
All Resource Governor objects.
All Server Audit Specifications.
All Custom Errors (User Defined Messages).
All Server Roles.
All Availability Groups.
PARAMETERS
-Append [<Switch>]
Append to the target file instead of overwriting.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-BatchSeparator [<String>]
Batch separator for scripting output. "GO" by default.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Credential [<Pscredential>]
Alternative Windows credentials for exporting Linked Servers and Credentials. Accepts credential objects
(Get-Credential)
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Exclude [<System.String[]>]
Exclude one or more objects to export
Databases
Logins
AgentServer
Credentials
LinkedServers
SpConfigure
CentralManagementServer
DatabaseMail
SysDbUserObjects
SystemTriggers
BackupDevices
Audits
Endpoints
ExtendedEvents
PolicyManagement
ResourceGovernor
ServerAuditSpecifications
CustomErrors
ServerRoles
AvailabilityGroups
ReplicationSettings
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludePassword [<Switch>]
If this switch is used, the scripts will not include passwords for Credentials, LinkedServers or Logins.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-IncludeDbMasterKey [<Switch>]
Exports the db master key then logs into the server to copy it to the $Path
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NoPrefix [<Switch>]
If this switch is used, the scripts will not include prefix information containing creator and datetime.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NoRecovery [<Switch>]
If this switch is used, databases will be left in the No Recovery state to enable further backups to be added.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Path [<String>]
Specifies the directory where the file or files will be exported.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ScriptingOption [<Microsoft.SqlServer.Management.Smo.ScriptingOptions>]
Add scripting options to scripting output for all objects except Registered Servers and Extended Events.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory -
Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
The target SQL Server instances
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: Export
Author: Chrissy LeMaire (@cl), netnerds.net
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Export-DbaInstance -SqlInstance sqlserver\\instance
All databases, logins, job objects and sp_configure options will be exported from
sqlserver\\instance to an automatically generated folder name in Documents.
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Export-DbaInstance -SqlInstance sqlcluster -Exclude Databases, Logins -Path C:\\dr\\sqlcluster
Exports everything but logins and database restore scripts to C:\\dr\\sqlcluster
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Export-DbaInstance -SqlInstance sqlcluster -Path C:\\servers\\ -NoPrefix
Exports everything to C:\\servers but scripts do not include prefix information.
RELATED LINKS
https://dbatools.io/Export-DbaInstance
SYNOPSIS
Exports SQL Server *ALL* database restore scripts, logins, database mail profiles/accounts, credentials, SQL Agent
objects, linked servers,
Central Management Server objects, server configuration settings (sp_configure), user objects in systems databases,
system triggers and backup devices from one SQL Server to another.
For more granular control, please use one of the -Exclude parameters and use the other functions available within
the dbatools module.
SYNTAX
Export-DbaInstance [-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [[-SqlCredential]
<Pscredential>] [[-Credential] <Pscredential>] [[-Path] <String>] [-NoRecovery <Switch>] [-IncludeDbMasterKey
<Switch>] [[-Exclude] <System.String[]>] [[-BatchSeparator] <String>] [-Append <Switch>] [[-ScriptingOption]
<Microsoft.SqlServer.Management.Smo.ScriptingOptions>] [-NoPrefix <Switch>] [-ExcludePassword <Switch>]
[-EnableException <Switch>] [<CommonParameters>]
DESCRIPTION
Export-DbaInstance consolidates most of the export scripts in dbatools into one command.
This is useful when you're looking to Export entire instances. It less flexible than using the underlying
functions.
Think of it as an easy button. Unless an -Exclude is specified, it exports:
All database restore scripts.
All logins.
All database mail objects.
All credentials.
All objects within the Job Server (SQL Agent).
All linked servers.
All groups and servers within Central Management Server.
All SQL Server configuration objects (everything in sp_configure).
All user objects in system databases.
All system triggers.
All system backup devices.
All Audits.
All Endpoints.
All Extended Events.
All Policy Management objects.
All Resource Governor objects.
All Server Audit Specifications.
All Custom Errors (User Defined Messages).
All Server Roles.
All Availability Groups.
PARAMETERS
-Append [<Switch>]
Append to the target file instead of overwriting.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-BatchSeparator [<String>]
Batch separator for scripting output. "GO" by default.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Credential [<Pscredential>]
Alternative Windows credentials for exporting Linked Servers and Credentials. Accepts credential objects
(Get-Credential)
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Exclude [<System.String[]>]
Exclude one or more objects to export
Databases
Logins
AgentServer
Credentials
LinkedServers
SpConfigure
CentralManagementServer
DatabaseMail
SysDbUserObjects
SystemTriggers
BackupDevices
Audits
Endpoints
ExtendedEvents
PolicyManagement
ResourceGovernor
ServerAuditSpecifications
CustomErrors
ServerRoles
AvailabilityGroups
ReplicationSettings
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludePassword [<Switch>]
If this switch is used, the scripts will not include passwords for Credentials, LinkedServers or Logins.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-IncludeDbMasterKey [<Switch>]
Exports the db master key then logs into the server to copy it to the $Path
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NoPrefix [<Switch>]
If this switch is used, the scripts will not include prefix information containing creator and datetime.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NoRecovery [<Switch>]
If this switch is used, databases will be left in the No Recovery state to enable further backups to be added.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Path [<String>]
Specifies the directory where the file or files will be exported.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ScriptingOption [<Microsoft.SqlServer.Management.Smo.ScriptingOptions>]
Add scripting options to scripting output for all objects except Registered Servers and Extended Events.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory -
Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
The target SQL Server instances
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: Export
Author: Chrissy LeMaire (@cl), netnerds.net
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Export-DbaInstance -SqlInstance sqlserver\\instance
All databases, logins, job objects and sp_configure options will be exported from
sqlserver\\instance to an automatically generated folder name in Documents.
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Export-DbaInstance -SqlInstance sqlcluster -Exclude Databases, Logins -Path C:\\dr\\sqlcluster
Exports everything but logins and database restore scripts to C:\\dr\\sqlcluster
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Export-DbaInstance -SqlInstance sqlcluster -Path C:\\servers\\ -NoPrefix
Exports everything to C:\\servers but scripts do not include prefix information.
RELATED LINKS
https://dbatools.io/Export-DbaInstance