commands

^

Export-DbaInstance

Author Chrissy LeMaire (@cl), netnerds.net
Availability Windows, Linux, macOS

 

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.

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 from backup' scripts. Note: if a database does not have a backup the 'restore from backup' script won't be generated. 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.

The exported files are written to a folder with a naming convention of "machinename$instance-yyyyMMddHHmmss".

This command supports the following use cases related to the output files:

  1. Export files to a new timestamped folder. This is the default behavior and results in a simple historical archive within the local filesystem.
  2. Export files to an existing folder and overwrite pre-existing files. This can be accomplished using the -Force parameter. This results in a single folder location with the latest exported files. These files can then be checked into a source control system if needed.

Syntax

Export-DbaInstance
    [-SqlInstance] <DbaInstanceParameter[]>
    [[-SqlCredential] <PSCredential>]
    [[-Credential] <PSCredential>]
    [[-Path] <String>]
    [-NoRecovery]
    [-IncludeDbMasterKey]
    [[-Exclude] <String[]>]
    [[-BatchSeparator] <String>]
    [[-ScriptingOption] <ScriptingOptions>]
    [-NoPrefix]
    [-ExcludePassword]
    [-Force]
    [-EnableException]
    [<CommonParameters>]

 

Examples

 

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. For example,
%userprofile%\Documents\DbatoolsExport\sqldev1$sqlcluster-20201108140000

Example: 2
PS C:\> Export-DbaInstance -SqlInstance sqlcluster -Exclude Databases, Logins -Path C:\dr\sqlcluster

Exports everything but logins and database restore scripts to a folder such as C:\dr\sqlcluster\sqldev1$sqlcluster-20201108140000

Example: 3
PS C:\> Export-DbaInstance -SqlInstance sqlcluster -Path C:\servers\ -NoPrefix

Exports everything to a folder such as C:\servers\sqldev1$sqlcluster-20201108140000 but scripts will not include prefix information.

Example: 4
PS C:\> Export-DbaInstance -SqlInstance sqlcluster -Path C:\servers\ -Force

Exports everything to a folder such as C:\servers\sqldev1$sqlcluster and will overwrite/refresh existing files in that folder. Note: when the -Force param is used the generated folder name will not
include a timestamp. This supports the use case of running Export-DbaInstance on a schedule and writing to the same dir each time.

Required Parameters

-SqlInstance

The target SQL Server instances

Alias
Required True
Pipeline true (ByValue)
Default Value

Optional Parameters

-SqlCredential

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.

Alias
Required False
Pipeline false
Default Value
-Credential

Alternative Windows credentials for exporting Linked Servers and Credentials. Accepts credential objects (Get-Credential)

Alias
Required False
Pipeline false
Default Value
-Path

Specifies the directory where the file or files will be exported.

Alias FilePath
Required False
Pipeline false
Default Value (Get-DbatoolsConfigValue -FullName 'Path.DbatoolsExport')
-NoRecovery

If this switch is used, databases will be left in the No Recovery state to enable further backups to be added.

Alias
Required False
Pipeline false
Default Value False
-IncludeDbMasterKey

Exports the db master key then logs into the server to copy it to the $Path

Alias
Required False
Pipeline false
Default Value False
-Exclude

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

Alias
Required False
Pipeline false
Default Value
Accepted Values AgentServer,Audits,AvailabilityGroups,BackupDevices,CentralManagementServer,Credentials,CustomErrors,DatabaseMail,Databases,Endpoints,ExtendedEvents,LinkedServers,Logins,PolicyManagement,ReplicationSettings,ResourceGovernor,ServerAuditSpecifications,ServerRoles,SpConfigure,SysDbUserObjects,SystemTriggers
-BatchSeparator

Batch separator for scripting output. "GO" by default based on (Get-DbatoolsConfigValue -FullName 'formatting.batchseparator').

Alias
Required False
Pipeline false
Default Value (Get-DbatoolsConfigValue -FullName 'formatting.batchseparator')
-ScriptingOption

Add scripting options to scripting output for all objects except Registered Servers and Extended Events.

Alias
Required False
Pipeline false
Default Value
-NoPrefix

If this switch is used, the scripts will not include prefix information containing creator and datetime.

Alias
Required False
Pipeline false
Default Value False
-ExcludePassword

If this switch is used, the scripts will not include passwords for Credentials, LinkedServers or Logins.

Alias
Required False
Pipeline false
Default Value False
-Force

Overwrite files in the location specified by -Path. Note: The Server Name is used when creating the folder structure.

Alias
Required False
Pipeline false
Default Value False
-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.

Alias
Required False
Pipeline false
Default Value False

 

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