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.
Invoke-DbaDbLogShipping sets up log shipping for one or more databases
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:
** 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.
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>]
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".
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".
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 |
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 |
Specifies which database(s) to configure for log shipping. The database must be in FULL recovery model.
Use this to target specific databases rather than setting up log shipping for all databases on the source instance.
Alias | |
Required | True |
Pipeline | true (ByValue) |
Default Value |
Specifies the network share path where transaction log backup files will be stored. Must be in UNC format (\server\share).
The function automatically creates a subdirectory for each database under this path. Both source and destination instances need access to this location.
Alias | BackupNetworkPath |
Required | True |
Pipeline | false |
Default Value |
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 |
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 |
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 |
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 |
Sets the local backup path on the source server when different from the shared path.
Use this when the source server accesses the backup location via a local path but other servers need to access it via the network share.
Alias | BackupLocalPath |
Required | False |
Pipeline | false |
Default Value |
Specifies the prefix for the SQL Agent backup job name that performs transaction log backups.
The database name is automatically appended to create the full job name. Defaults to 'LSBackup_' if not specified.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Sets how long backup files are retained before deletion, specified in minutes.
Defaults to 4320 minutes (72 hours). Consider storage capacity and recovery requirements when setting this value.
Alias | |
Required | False |
Pipeline | false |
Default Value | 0 |
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 |
Creates the backup job schedule in a disabled state, preventing automatic execution.
Use this when you want to manually control when log shipping backup jobs start running.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Controls how often the backup job runs. Accepts 'Daily' (most common), 'AgentStart', or 'IdleComputer'.
Daily scheduling allows for regular transaction log backups to maintain the log shipping chain.
Alias | |
Required | False |
Pipeline | false |
Default Value | |
Accepted Values | Daily,Weekly,AgentStart,IdleComputer |
The number of type periods to occur between each execution of the backup job.
Alias | |
Required | False |
Pipeline | false |
Default Value |
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 |
Specifies the interval between backup job executions within a day when using Minutes, Seconds, or Hours frequency.
For example, setting 15 with FrequencySubdayType of 'Minutes' creates backups every 15 minutes.
Alias | |
Required | False |
Pipeline | false |
Default Value | 0 |
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 |
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 |
The date on which execution of a job can begin.
Alias | |
Required | False |
Pipeline | false |
Default Value |
The date on which execution of a job can stop.
Alias | |
Required | False |
Pipeline | false |
Default Value |
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 |
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 |
Sets the alert threshold in minutes for detecting backup delays. An alert is raised if no backup occurs within this timeframe.
Defaults to 60 minutes. Use shorter intervals for critical databases requiring frequent log backups.
Alias | |
Required | False |
Pipeline | false |
Default Value | 0 |
Enables backup compression for transaction log backups to reduce file size and network transfer time.
Only available on SQL Server 2008 and later. Uses server default compression setting if not specified.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Specifies the destination folder path where backup files are copied on the secondary server.
The function creates a database-specific subdirectory under this path. Defaults to the secondary server's backup directory if not provided.
Alias | |
Required | False |
Pipeline | false |
Default Value |
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 |
The copy retention period in minutes. Default is 4320 / 72 hours
Alias | |
Required | False |
Pipeline | false |
Default Value | 0 |
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 |
Parameter to set the copy schedule to disabled upon creation.
By default the schedule is enabled.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
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 |
The number of type periods to occur between each execution of the copy job.
Alias | |
Required | False |
Pipeline | false |
Default Value |
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 |
The number of subday type periods to occur between each execution of the copy job.
Alias | |
Required | False |
Pipeline | false |
Default Value | 0 |
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 |
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 |
The date on which execution of a job can begin.
Alias | |
Required | False |
Pipeline | false |
Default Value |
The date on which execution of a job can stop.
Alias | |
Required | False |
Pipeline | false |
Default Value |
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 |
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 |
Forces disconnection of users from the secondary database during transaction log restore operations.
Use this with standby mode when you need to ensure restores complete successfully despite active connections.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Specifies the path to an existing full database backup to initialize the secondary database.
Use this when you have a recent backup available and want to avoid creating a new full backup for initialization.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Creates a new full backup of the source database and restores it to initialize the secondary database.
Use this when no existing backup is available or when you want to ensure the secondary starts with the most current data.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Sets how long log shipping history information is kept in the monitor server, specified in minutes.
Defaults to 14420 minutes (approximately 10 days). Longer retention provides more historical data for troubleshooting.
Alias | |
Required | False |
Pipeline | false |
Default Value | 0 |
Keeps the secondary database in NORECOVERY mode, making it unavailable for read access but ready for continuous log restores.
This is the default mode and maintains the fastest restore performance for log shipping.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Skips secondary database initialization, assuming the database already exists in NORECOVERY mode on the destination.
Use this when you have manually restored the database or used a different method to initialize it.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Specifies the SQL Server instance that monitors the primary server's log shipping operations.
Defaults to the source instance itself. Use a dedicated monitor server in production environments for centralized monitoring.
Alias | |
Required | False |
Pipeline | false |
Default Value |
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 |
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 |
Enables the Threshold alert for the primary database
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Sets the destination folder for database data files during secondary database initialization.
Only used with GenerateFullBackup or UseExistingFullBackup. Defaults to the secondary instance's default data directory if not specified.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Sets the destination folder for database log files during secondary database initialization.
Only used with GenerateFullBackup or UseExistingFullBackup. Defaults to the secondary instance's default log directory if not specified.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Introduces a delay in minutes before applying transaction log restores on the secondary database.
Defaults to 0 (no delay). Use this to create a time buffer for recovering from accidental data changes on the primary.
Alias | |
Required | False |
Pipeline | false |
Default Value | 0 |
Sets the alert threshold in minutes for detecting restore operation delays on the secondary database.
An alert is generated if no restore occurs within this timeframe. Defaults to 45 minutes.
Alias | |
Required | False |
Pipeline | false |
Default Value | 0 |
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 |
The backup retention period in minutes. Default is 4320 / 72 hours
Alias | |
Required | False |
Pipeline | false |
Default Value | 0 |
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 |
Parameter to set the restore schedule to disabled upon creation.
By default the schedule is enabled.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
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 |
The number of type periods to occur between each execution of the restore job.
Alias | |
Required | False |
Pipeline | false |
Default Value |
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 |
The number of subday type periods to occur between each execution of the restore job.
Alias | |
Required | False |
Pipeline | false |
Default Value | 0 |
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 |
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 |
The date on which execution of a job can begin.
Alias | |
Required | False |
Pipeline | false |
Default Value |
The date on which execution of a job can stop.
Alias | |
Required | False |
Pipeline | false |
Default Value |
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 |
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 |
Specifies the maximum time in minutes allowed between restore operations before triggering an alert.
Defaults to 45 minutes. Set this based on your RTO requirements and backup frequency.
Alias | |
Required | False |
Pipeline | false |
Default Value | 0 |
Adds a prefix to the secondary database name to distinguish it from the primary database.
Useful when the secondary database resides on the same instance as the primary or for naming conventions.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Adds a suffix to the secondary database name to distinguish it from the primary database.
Common suffixes include '_LS' for log shipping or '_DR' for disaster recovery. Automatically applied when source and destination are the same instance.
Alias | |
Required | False |
Pipeline | false |
Default Value |
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 |
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 |
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 |
Enables the Threshold alert for the secondary database
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Places the secondary database in STANDBY mode, allowing read-only access for reporting purposes.
Users are disconnected during log restores. Alternative to NORECOVERY mode when you need read access to secondary data.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Specifies the directory where standby files (.tuf) are created when using STANDBY mode.
Required when using the Standby parameter. These files contain uncommitted transactions that are temporarily backed out during restore operations.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Uses the most recent full backup from backup history to initialize the secondary database.
The function automatically locates and uses the latest full backup of the source database for initialization.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Specifies a folder containing backup files (full and/or differential) to initialize the secondary database.
The function processes all backup files in the folder to bring the secondary database up to the latest available point in time.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Bypasses confirmations and applies default values for missing parameters like copy destination folder.
Also removes existing schedules with the same name and sets automatic database suffix when source and destination instances are identical.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
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 |
Shows what would happen if the command were to run. No actions are actually performed.
Alias | wi |
Required | False |
Pipeline | false |
Default Value |
Prompts you for confirmation before executing any changing operations within the command.
Alias | cf |
Required | False |
Pipeline | false |
Default Value |