commands

Clear
^

Invoke-DbaDbLogShipping
Author Sander Stad (@sqlstad), sqlstad.nl
Availability Windows, Linux, macOS

 

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

Synopsis

Invoke-DbaDbLogShipping sets up log shipping for one or more databases

Description

Invoke-DbaDbLogShipping helps to easily set up log shipping for one or more databases.

This function will make a lot of decisions for you assuming you want default values like a daily interval for the schedules with a 15 minute interval on the day.
There are some settings that cannot be made by the function and they need to be prepared before the function is executed.

The following settings need to be made before log shipping can be initiated:

  • Backup destination (the folder and the privileges)
  • Copy destination (the folder and the privileges)
  • Privileges
    Make sure your agent service on both the primary and the secondary instance is an Active Directory account.
    Also have the credentials ready to set the folder permissions

** Network share
The backup destination needs to be shared and have the share privileges of FULL CONTROL to Everyone.

** NTFS permissions
The backup destination must have at least read/write permissions for the primary instance agent account.
The backup destination must have at least read permissions for the secondary instance agent account.
The copy destination must have at least read/write permission for the secondary instance agent account.

Syntax

Invoke-DbaDbLogShipping
    [-SourceSqlInstance] <DbaInstanceParameter>
    [-DestinationSqlInstance] <DbaInstanceParameter[]>
    [[-SourceSqlCredential] <PSCredential>]
    [[-SourceCredential] <PSCredential>]
    [[-DestinationSqlCredential] <PSCredential>]
    [[-DestinationCredential] <PSCredential>]
    [-Database] <Object[]>
    [-SharedPath] <String>
    [[-LocalPath] <String>]
    [[-BackupJob] <String>]
    [[-BackupRetention] <Int32>]
    [[-BackupSchedule] <String>]
    [-BackupScheduleDisabled]
    [[-BackupScheduleFrequencyType] <Object>]
    [[-BackupScheduleFrequencyInterval] <Object[]>]
    [[-BackupScheduleFrequencySubdayType] <Object>]
    [[-BackupScheduleFrequencySubdayInterval] <Int32>]
    [[-BackupScheduleFrequencyRelativeInterval] <Object>]
    [[-BackupScheduleFrequencyRecurrenceFactor] <Int32>]
    [[-BackupScheduleStartDate] <String>]
    [[-BackupScheduleEndDate] <String>]
    [[-BackupScheduleStartTime] <String>]
    [[-BackupScheduleEndTime] <String>]
    [[-BackupThreshold] <Int32>]
    [-CompressBackup]
    [[-CopyDestinationFolder] <String>]
    [[-CopyJob] <String>]
    [[-CopyRetention] <Int32>]
    [[-CopySchedule] <String>]
    [-CopyScheduleDisabled]
    [[-CopyScheduleFrequencyType] <Object>]
    [[-CopyScheduleFrequencyInterval] <Object[]>]
    [[-CopyScheduleFrequencySubdayType] <Object>]
    [[-CopyScheduleFrequencySubdayInterval] <Int32>]
    [[-CopyScheduleFrequencyRelativeInterval] <Object>]
    [[-CopyScheduleFrequencyRecurrenceFactor] <Int32>]
    [[-CopyScheduleStartDate] <String>]
    [[-CopyScheduleEndDate] <String>]
    [[-CopyScheduleStartTime] <String>]
    [[-CopyScheduleEndTime] <String>]
    [-DisconnectUsers]
    [[-FullBackupPath] <String>]
    [-GenerateFullBackup]
    [[-HistoryRetention] <Int32>]
    [-NoRecovery]
    [-NoInitialization]
    [[-PrimaryMonitorServer] <String>]
    [[-PrimaryMonitorCredential] <PSCredential>]
    [[-PrimaryMonitorServerSecurityMode] <Object>]
    [-PrimaryThresholdAlertEnabled]
    [[-RestoreDataFolder] <String>]
    [[-RestoreLogFolder] <String>]
    [[-RestoreDelay] <Int32>]
    [[-RestoreAlertThreshold] <Int32>]
    [[-RestoreJob] <String>]
    [[-RestoreRetention] <Int32>]
    [[-RestoreSchedule] <String>]
    [-RestoreScheduleDisabled]
    [[-RestoreScheduleFrequencyType] <Object>]
    [[-RestoreScheduleFrequencyInterval] <Object[]>]
    [[-RestoreScheduleFrequencySubdayType] <Object>]
    [[-RestoreScheduleFrequencySubdayInterval] <Int32>]
    [[-RestoreScheduleFrequencyRelativeInterval] <Object>]
    [[-RestoreScheduleFrequencyRecurrenceFactor] <Int32>]
    [[-RestoreScheduleStartDate] <String>]
    [[-RestoreScheduleEndDate] <String>]
    [[-RestoreScheduleStartTime] <String>]
    [[-RestoreScheduleEndTime] <String>]
    [[-RestoreThreshold] <Int32>]
    [[-SecondaryDatabasePrefix] <String>]
    [[-SecondaryDatabaseSuffix] <String>]
    [[-SecondaryMonitorServer] <String>]
    [[-SecondaryMonitorCredential] <PSCredential>]
    [[-SecondaryMonitorServerSecurityMode] <Object>]
    [-SecondaryThresholdAlertEnabled]
    [-Standby]
    [[-StandbyDirectory] <String>]
    [-UseExistingFullBackup]
    [[-UseBackupFolder] <String>]
    [-Force]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

 

Examples

 

Example: 1
PS C:\> $params = @{
>> SourceSqlInstance = 'sql1'
>> DestinationSqlInstance = 'sql2'
>> Database = 'db1'
>> SharedPath= '\\sql1\logshipping'
>> LocalPath= 'D:\Data\logshipping'
>> BackupScheduleFrequencyType = 'daily'
>> BackupScheduleFrequencyInterval = 1
>> CompressBackup = $true
>> CopyScheduleFrequencyType = 'daily'
>> CopyScheduleFrequencyInterval = 1
>> GenerateFullBackup = $true
>> RestoreScheduleFrequencyType = 'daily'
>> RestoreScheduleFrequencyInterval = 1
>> SecondaryDatabaseSuffix = 'LS'
>> CopyDestinationFolder = '\\sql2\logshippingdest'
>> Force = $true
>> }
>>
PS C:\> Invoke-DbaDbLogShipping @params

Sets up log shipping for database "db1" with the backup path to a network share allowing local backups.
It creates daily schedules for the backup, copy and restore job with all the defaults to be executed every 15 minutes daily.
The secondary database will be called "db1_LS".

Example: 2
PS C:\> $params = @{
>> SourceSqlInstance = 'sql1'
>> DestinationSqlInstance = 'sql2'
>> Database = 'db1'
>> SharedPath= '\\sql1\logshipping'
>> GenerateFullBackup = $true
>> Force = $true
>> }
>>
PS C:\> Invoke-DbaDbLogShipping @params

Sets up log shipping with all defaults except that a backup file is generated.
The script will show a message that the copy destination has not been supplied and asks if you want to use the default which would be the backup directory of the secondary server with the folder
"logshipping" i.e. "D:\SQLBackup\Logshiping".

Required Parameters

-SourceSqlInstance

Source SQL Server instance which contains the databases to be log shipped.
You must have sysadmin access and server version must be SQL Server version 2000 or greater.

Alias SourceServerInstance,SourceSqlServerSqlServer,Source
Required True
Pipeline false
Default Value
-DestinationSqlInstance

Destination SQL Server instance which contains the databases to be log shipped.
You must have sysadmin access and server version must be SQL Server version 2000 or greater.

Alias DestinationServerInstance,DestinationSqlServer,Destination
Required True
Pipeline false
Default Value
-Database

Database to set up log shipping for.

Alias
Required True
Pipeline true (ByValue)
Default Value
-SharedPath

The backup unc path to place the backup files. This is the root directory.
A directory with the name of the database will be created in this path.

Alias BackupNetworkPath
Required True
Pipeline false
Default Value

Optional Parameters

-SourceSqlCredential

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
-SourceCredential

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
-DestinationSqlCredential

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
-DestinationCredential

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
-LocalPath

If the backup path is locally for the source server you can also set this value.
Alias BackupLocalPath
Required False
Pipeline false
Default Value
-BackupJob

Name of the backup that will be created in the SQL Server agent.
The parameter works as a prefix where the name of the database will be added to the backup job name.
The default is "LSBackup_[databasename]"
Alias
Required False
Pipeline false
Default Value
-BackupRetention

The backup retention period in minutes. Default is 4320 / 72 hours
Alias
Required False
Pipeline false
Default Value 0
-BackupSchedule

Name of the backup schedule created for the backup job.
The parameter works as a prefix where the name of the database will be added to the backup job schedule name.
Default is "LSBackupSchedule_[databasename]"
Alias
Required False
Pipeline false
Default Value
-BackupScheduleDisabled

Parameter to set the backup schedule to disabled upon creation.
By default the schedule is enabled.
Alias
Required False
Pipeline false
Default Value False
-BackupScheduleFrequencyType

A value indicating when a job is to be executed.
Allowed values are "Daily", "AgentStart", "IdleComputer"
Alias
Required False
Pipeline false
Default Value
Accepted Values Daily,Weekly,AgentStart,IdleComputer
-BackupScheduleFrequencyInterval

The number of type periods to occur between each execution of the backup job.
Alias
Required False
Pipeline false
Default Value
-BackupScheduleFrequencySubdayType

Specifies the units for the sub-day FrequencyInterval.
Allowed values are "Time", "Seconds", "Minutes", "Hours"
Alias
Required False
Pipeline false
Default Value
Accepted Values Time,Seconds,Minutes,Hours
-BackupScheduleFrequencySubdayInterval

The number of sub-day type periods to occur between each execution of the backup job.
Alias
Required False
Pipeline false
Default Value 0
-BackupScheduleFrequencyRelativeInterval

A job's occurrence of FrequencyInterval in each month, if FrequencyInterval is 32 (monthlyrelative).
Alias
Required False
Pipeline false
Default Value
Accepted Values Unused,First,Second,Third,Fourth,Last
-BackupScheduleFrequencyRecurrenceFactor

The number of weeks or months between the scheduled execution of a job. FrequencyRecurrenceFactor is used only if FrequencyType is 8, "Weekly", 16, "Monthly", 32 or "MonthlyRelative".
Alias
Required False
Pipeline false
Default Value 0
-BackupScheduleStartDate

The date on which execution of a job can begin.
Alias
Required False
Pipeline false
Default Value
-BackupScheduleEndDate

The date on which execution of a job can stop.
Alias
Required False
Pipeline false
Default Value
-BackupScheduleStartTime

The time on any day to begin execution of a job. Format HHMMSS / 24 hour clock.
Example: '010000' for 01:00:00 AM.
Example: '140000' for 02:00:00 PM.
Alias
Required False
Pipeline false
Default Value
-BackupScheduleEndTime

The time on any day to end execution of a job. Format HHMMSS / 24 hour clock.
Example: '010000' for 01:00:00 AM.
Example: '140000' for 02:00:00 PM.
Alias
Required False
Pipeline false
Default Value
-BackupThreshold

Is the length of time, in minutes, after the last backup before a threshold alert error is raised.
The default is 60.
Alias
Required False
Pipeline false
Default Value 0
-CompressBackup

Do the backups need to be compressed. By default the backups are not compressed.
Alias
Required False
Pipeline false
Default Value False
-CopyDestinationFolder

The path to copy the transaction log backup files to. This is the root directory.
A directory with the name of the database will be created in this path.
Alias
Required False
Pipeline false
Default Value
-CopyJob

Name of the copy job that will be created in the SQL Server agent.
The parameter works as a prefix where the name of the database will be added to the copy job name.
The default is "LSBackup_[databasename]"
Alias
Required False
Pipeline false
Default Value
-CopyRetention

The copy retention period in minutes. Default is 4320 / 72 hours
Alias
Required False
Pipeline false
Default Value 0
-CopySchedule

Name of the backup schedule created for the copy job.
The parameter works as a prefix where the name of the database will be added to the copy job schedule name.
Default is "LSCopy_[DestinationServerName]_[DatabaseName]"
Alias
Required False
Pipeline false
Default Value
-CopyScheduleDisabled

Parameter to set the copy schedule to disabled upon creation.
By default the schedule is enabled.
Alias
Required False
Pipeline false
Default Value False
-CopyScheduleFrequencyType

A value indicating when a job is to be executed.
Allowed values are "Daily", "AgentStart", "IdleComputer"
Alias
Required False
Pipeline false
Default Value
Accepted Values Daily,Weekly,AgentStart,IdleComputer
-CopyScheduleFrequencyInterval

The number of type periods to occur between each execution of the copy job.
Alias
Required False
Pipeline false
Default Value
-CopyScheduleFrequencySubdayType

Specifies the units for the subday FrequencyInterval.
Allowed values are "Time", "Seconds", "Minutes", "Hours"
Alias
Required False
Pipeline false
Default Value
Accepted Values Time,Seconds,Minutes,Hours
-CopyScheduleFrequencySubdayInterval

The number of subday type periods to occur between each execution of the copy job.
Alias
Required False
Pipeline false
Default Value 0
-CopyScheduleFrequencyRelativeInterval

A job's occurrence of FrequencyInterval in each month, if FrequencyInterval is 32 (monthlyrelative).
Alias
Required False
Pipeline false
Default Value
Accepted Values Unused,First,Second,Third,Fourth,Last
-CopyScheduleFrequencyRecurrenceFactor

The number of weeks or months between the scheduled execution of a job. FrequencyRecurrenceFactor is used only if FrequencyType is 8, "Weekly", 16, "Monthly", 32 or "MonthlyRelative".
Alias
Required False
Pipeline false
Default Value 0
-CopyScheduleStartDate

The date on which execution of a job can begin.
Alias
Required False
Pipeline false
Default Value
-CopyScheduleEndDate

The date on which execution of a job can stop.
Alias
Required False
Pipeline false
Default Value
-CopyScheduleStartTime

The time on any day to begin execution of a job. Format HHMMSS / 24 hour clock.
Example: '010000' for 01:00:00 AM.
Example: '140000' for 02:00:00 PM.
Alias
Required False
Pipeline false
Default Value
-CopyScheduleEndTime

The time on any day to end execution of a job. Format HHMMSS / 24 hour clock.
Example: '010000' for 01:00:00 AM.
Example: '140000' for 02:00:00 PM.
Alias
Required False
Pipeline false
Default Value
-DisconnectUsers

If this parameter is set in combinations of standby the users will be disconnected during restore.
Alias
Required False
Pipeline false
Default Value False
-FullBackupPath

Path to an existing full backup. Use this when an existing backup needs to used to initialize the database on the secondary instance.
Alias
Required False
Pipeline false
Default Value
-GenerateFullBackup

If the database is not initialized on the secondary instance it can be done by creating a new full backup and
restore it for you.
Alias
Required False
Pipeline false
Default Value False
-HistoryRetention

Is the length of time in minutes in which the history is retained.
The default value is 14420
Alias
Required False
Pipeline false
Default Value 0
-NoRecovery

If this parameter is set the database will be in recovery mode. The database will not be readable.
This setting is default.
Alias
Required False
Pipeline false
Default Value False
-NoInitialization

If this parameter is set the secondary database will not be initialized.
The database needs to be on the secondary instance in recovery mode.
Alias
Required False
Pipeline false
Default Value False
-PrimaryMonitorServer

Is the name of the monitor server for the primary server.
Defaults to monitor on the instance provided via SourceSqlInstance param.
Alias
Required False
Pipeline false
Default Value
-PrimaryMonitorCredential

Allows you to login to enter a secure credential. Only needs to be used when the PrimaryMonitorServerSecurityMode is 0 or "sqlserver"
To use: $scred = Get-Credential, then pass $scred object to the -PrimaryMonitorCredential parameter.
Alias
Required False
Pipeline false
Default Value
-PrimaryMonitorServerSecurityMode

The security mode used to connect to the monitor server for the primary server. Allowed values are 0, "sqlserver", 1, "windows"
The default is 1 or Windows.
Alias
Required False
Pipeline false
Default Value
Accepted Values 0,sqlserver,1,windows
-PrimaryThresholdAlertEnabled

Enables the Threshold alert for the primary database
Alias
Required False
Pipeline false
Default Value False
-RestoreDataFolder

Folder to be used to restore the database data files. Only used when parameter GenerateFullBackup or UseExistingFullBackup are set.
If the parameter is not set the default data folder of the secondary instance will be used.
If the folder is set but doesn't exist we will try to create the folder.
Alias
Required False
Pipeline false
Default Value
-RestoreLogFolder

Folder to be used to restore the database log files. Only used when parameter GenerateFullBackup or UseExistingFullBackup are set.
If the parameter is not set the default transaction log folder of the secondary instance will be used.
If the folder is set but doesn't exist we will try to create the folder.
Alias
Required False
Pipeline false
Default Value
-RestoreDelay

In case a delay needs to be set for the restore.
The default is 0.
Alias
Required False
Pipeline false
Default Value 0
-RestoreAlertThreshold

The amount of minutes after which an alert will be raised is no restore has taken place.
The default is 45 minutes.
Alias
Required False
Pipeline false
Default Value 0
-RestoreJob

Name of the restore job that will be created in the SQL Server agent.
The parameter works as a prefix where the name of the database will be added to the restore job name.
The default is "LSRestore_[databasename]"
Alias
Required False
Pipeline false
Default Value
-RestoreRetention

The backup retention period in minutes. Default is 4320 / 72 hours
Alias
Required False
Pipeline false
Default Value 0
-RestoreSchedule

Name of the backup schedule created for the restore job.
The parameter works as a prefix where the name of the database will be added to the restore job schedule name.
Default is "LSRestore_[DestinationServerName]_[DatabaseName]"
Alias
Required False
Pipeline false
Default Value
-RestoreScheduleDisabled

Parameter to set the restore schedule to disabled upon creation.
By default the schedule is enabled.
Alias
Required False
Pipeline false
Default Value False
-RestoreScheduleFrequencyType

A value indicating when a job is to be executed.
Allowed values are "Daily", "AgentStart", "IdleComputer"
Alias
Required False
Pipeline false
Default Value
Accepted Values Daily,Weekly,AgentStart,IdleComputer
-RestoreScheduleFrequencyInterval

The number of type periods to occur between each execution of the restore job.
Alias
Required False
Pipeline false
Default Value
-RestoreScheduleFrequencySubdayType

Specifies the units for the subday FrequencyInterval.
Allowed values are "Time", "Seconds", "Minutes", "Hours"
Alias
Required False
Pipeline false
Default Value
Accepted Values Time,Seconds,Minutes,Hours
-RestoreScheduleFrequencySubdayInterval

The number of subday type periods to occur between each execution of the restore job.
Alias
Required False
Pipeline false
Default Value 0
-RestoreScheduleFrequencyRelativeInterval

A job's occurrence of FrequencyInterval in each month, if FrequencyInterval is 32 (monthlyrelative).
Alias
Required False
Pipeline false
Default Value
Accepted Values Unused,First,Second,Third,Fourth,Last
-RestoreScheduleFrequencyRecurrenceFactor

The number of weeks or months between the scheduled execution of a job. FrequencyRecurrenceFactor is used only if FrequencyType is 8, "Weekly", 16, "Monthly", 32 or "MonthlyRelative".
Alias
Required False
Pipeline false
Default Value 0
-RestoreScheduleStartDate

The date on which execution of a job can begin.
Alias
Required False
Pipeline false
Default Value
-RestoreScheduleEndDate

The date on which execution of a job can stop.
Alias
Required False
Pipeline false
Default Value
-RestoreScheduleStartTime

The time on any day to begin execution of a job. Format HHMMSS / 24 hour clock.
Example: '010000' for 01:00:00 AM.
Example: '140000' for 02:00:00 PM.
Alias
Required False
Pipeline false
Default Value
-RestoreScheduleEndTime

The time on any day to end execution of a job. Format HHMMSS / 24 hour clock.
Example: '010000' for 01:00:00 AM.
Example: '140000' for 02:00:00 PM.
Alias
Required False
Pipeline false
Default Value
-RestoreThreshold

The number of minutes allowed to elapse between restore operations before an alert is generated.
The default value = 45
Alias
Required False
Pipeline false
Default Value 0
-SecondaryDatabasePrefix

The secondary database can be renamed to include a prefix.
Alias
Required False
Pipeline false
Default Value
-SecondaryDatabaseSuffix

The secondary database can be renamed to include a suffix.
Alias
Required False
Pipeline false
Default Value
-SecondaryMonitorServer

Is the name of the monitor server for the secondary server.
Defaults to monitor on the instance provided via DestinationSqlInstance param.
Alias
Required False
Pipeline false
Default Value
-SecondaryMonitorCredential

Allows you to login to enter a secure credential. Only needs to be used when the SecondaryMonitorServerSecurityMode is 0 or "sqlserver"
To use: $scred = Get-Credential, then pass $scred object to the -SecondaryMonitorCredential parameter.
Alias
Required False
Pipeline false
Default Value
-SecondaryMonitorServerSecurityMode

The security mode used to connect to the monitor server for the secondary server. Allowed values are 0, "sqlserver", 1, "windows"
The default is 1 or Windows.
Alias
Required False
Pipeline false
Default Value
Accepted Values 0,sqlserver,1,windows
-SecondaryThresholdAlertEnabled

Enables the Threshold alert for the secondary database
Alias
Required False
Pipeline false
Default Value False
-Standby

If this parameter is set the database will be set to standby mode making the database readable.
If not set the database will be in recovery mode.
Alias
Required False
Pipeline false
Default Value False
-StandbyDirectory

Directory to place the standby file(s) in
Alias
Required False
Pipeline false
Default Value
-UseExistingFullBackup

If the database is not initialized on the secondary instance it can be done by selecting an existing full backup
and restore it for you.
Alias
Required False
Pipeline false
Default Value False
-UseBackupFolder

This enables the user to specify a specific backup folder containing one or more backup files to initialize the database on the secondary instance.
Alias
Required False
Pipeline false
Default Value
-Force

The force parameter will ignore some errors in the parameters and assume defaults.
It will also remove the any present schedules with the same name for the specific job.
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
-WhatIf

Shows what would happen if the command were to run. No actions are actually performed.
Alias wi
Required False
Pipeline false
Default Value
-Confirm

Prompts you for confirmation before executing any changing operations within the command.
Alias cf
Required False
Pipeline false
Default Value