Upgrade Log Shipping
To upgrade log shipping plans to LiteSpeed 8.9.8
-
Disable the LiteSpeed log shipping jobs on all servers where you plan to upgrade a previous version of LiteSpeed, before installing a new version of LiteSpeed.
NOTE: Instead of disabling LiteSpeed jobs, you can stop SQL Server Agent on servers. If you select this option, no other backups, database maintenance, and third-party application jobs will be able to run. To disable SQL Server Agent, open SQL Server Configuration Manager, right-click SQL Server Agent and select Stop.
- Upgrade LiteSpeed for SQL Server on all instances involved in log shipping.
-
Enable the LiteSpeed log shipping jobs.
NOTE: If you stopped SQL Server Agent instead of disabling the jobs, restart it at this time. To restart SQL Server Agent, open SQL Server Configuration Manager, right-click SQL Server Agent and select Start.
For more information about upgrading LiteSpeed, see the Upgrade scenario in the LiteSpeed 8.9.8 Installation Guide.
To upgrade LiteSpeed log shipping plans when upgrading from SQL Server 2005 to 2008 or from SQL Server 2012 to 2014
Make sure everything is ok with the plan after the in-place upgrade. If it is not, reinitialize the plan. For more information, see Reinitialize the plan.
NOTE: If you want to migrate a database to a new server and want to continue shipping logs for this database, you will need to create a new plan. For more information, see Create Log Shipping Plans.
Log Shipping Stored Procedures
You can manually set up and edit LiteSpeed log shipping plans using the stored procedures.
Syntax
- The log shipping stored procedures are executed against the LiteSpeed local repository. (Usually, LiteSpeedLocal.)
- You must run the stored procedures in a set order: set up the publisher before you set up any subscribers. It is recommended that you use the Create LiteSpeed Log Shipping Plan wizard to generate the setup scripts and then review them and make necessary edits. For more information, see Create Log Shipping Plans.
- In some cases, you need to supply the plan id (@PlanID) to specify the plan. Run the following select statement on the publisher server: SELECT * FROM LiteSpeedLocal.dbo.LogShippingPlan and copy the relevant plan id. Alternately, right-click a plan in the server tree in the LiteSpeed UI Console and select Properties.
Following are several examples. For more information, see Procedures. about the stored procedures and accepted arguments.
Set up Log Shipping
/* this script executes on publisher PublisherServer. We declare a variable to hold the PlanID output: */
declare @plan_id as uniqueidentifier
declare @backup_job_id as uniqueidentifier
exec LogShipping_Add_PrimaryDatabase
@PlanName=N'Test_Plan',
@PlanDescription=N'Shipping PublisherDB log backups from PublisherServer to SubscriberServer',
@Database = N'PublisherDB',
@PlanID = @plan_id output,
@BackupShare = N'\\PublisherServer\Share',
@BackupJobID = @backup_job_id output,
@BackupPeriod = 15,
@BackupRetentionPeriod = 1440,
@BackupAlertThreshold = 45,
@BackupDirectory = N'C:\Share\',
@MirrorBackupDirectory = N'C:\Share\',
@PerformBackup = 1
select @plan_id
/* this script executes on publisher PublisherServer: */
exec LogShipping_Add_PrimarySubscriber
@PrimaryDatabase = N'PublisherDB',
@SecondaryServer = N'SubscriberServer',
@SecondaryDatabase = N'SubscriberDB'
/* this script executes on subscriber SubscriberServer: */
declare @plan_id as uniqueidentifier
set @plan_id = N'SPECIFY PLAN ID FROM PUBLISHER' -- Specify plan ID, that was returned from publisher
declare @fname as nvarchar(256)
set @fname = N'\\PublisherServer\Share\' + CONVERT(nvarchar(40), @plan_id) +'_PublisherDB.bak'
exec master.dbo.xp_restore_database
@database = N'SubscriberDB',
@filename = @fname,
@filenumber = 1,
@with = N'REPLACE'
, @with = N'NORECOVERY'
, @with = N'MOVE N''PublisherDB'' TO N''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SubscriberDB.mdf'''
, @with = N'MOVE N''PublisherDB_log'' TO N''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SubscriberDB_Log.ldf'''
/* this script executes on subscriber SubscriberServer: */
declare @plan_id as uniqueidentifier
set @plan_id = N'SPECIFY PLAN ID FROM PUBLISHER' -- Specify plan ID, that was returned from publisher
declare @restore_job as uniqueidentifier
declare @secondary as uniqueidentifier
exec LogShipping_Init_Subscriber
@PlanName=N'Test_Plan',
@PlanDescription=N'Shipping PublisherDB log backups from PublisherServer to SubscriberServer',
@PlanID = @plan_id,
@PrimaryServer = N'PublisherServer',
@PrimaryDatabase = N'PublisherDB',
@BackupSourceDirectory = N'\\PublisherServer\Share',
@BackupDestinationDirectory = N'c:\temp',
@SecondaryDatabase = N'SubscriberDB',
@RestoreJobID = @restore_job output,
@SecondaryID = @secondary output,
@RestoreDelay = 0,
@FileRetentionPeriod = 1440,
@CopyPeriod = 15,
@RestorePeriod = 15,
@RestoreMode = 0,
@DisconnectUsers = 0,
@RestoreAlertThreshold = 30,
@RestoreAffinity = 0
Add Another Subscriber
/* this script executes on publisher PublisherServer: */
LogShipping_Update_PrimaryDatabase
@PlanID = N'c2a43886-5da2-40ef-b03d-693ebf185db6',
@PlanDescription=NShipping PublisherDB log backups from PublisherServer to SubscriberServer and AnotherPublisherServer',
@PerformBackup = 1
/* this script executes on publisher PublisherServer: */
exec LogShipping_Add_PrimarySubscriber
@PrimaryDatabase = N'PublisherDB',
@SecondaryServer = N'AnotherSubscriberServer',
@SecondaryDatabase = N'AnotherSubscriberDB'
/* this script executes on subscriber AnotherSubscriberServer: */
declare @fname as nvarchar(256)
set @fname = N'\\PublisherServer\Share\c2a43886-5da2-40ef-b03d-693ebf185db6_PublisherDB.bak'
exec master.dbo.xp_restore_database
@database = N'AnotherSubscriberDB',
@filename = @fname,
@filenumber = 1,
@with = N'REPLACE'
, @with = N'NORECOVERY'
, @with = N'MOVE N''PublisherDB'' TO N''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AnotherSubscriberDB.mdf'''
, @with = N'MOVE N''PublisherDB_log'' TO N''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AnotherSubscriberDB_Log.ldf'''
/* this script executes on subscriber AnotherSubscriberServer: */
declare @restore_job as uniqueidentifier
declare @secondary as uniqueidentifier
exec LogShipping_Init_Subscriber
@PlanName=N'Test_Plan',
@PlanDescription=NShipping PublisherDB log backups from PublisherServer to SubscriberServer and AnotherPublisherServer',
@PlanID = N'c2a43886-5da2-40ef-b03d-693ebf185db6',
@PrimaryServer = N'PublisherServer',
@PrimaryDatabase = N'PublisherDB',
@BackupSourceDirectory = N'\\PublisherServer\Share\',
@BackupDestinationDirectory = N'c:\temp',
@SecondaryDatabase = N'AnotherSubscriberDB',
@RestoreJobID = @restore_job output,
@SecondaryID = @secondary output,
@RestoreDelay = 0,
@FileRetentionPeriod = 1440,
@CopyPeriod = 15,
@RestorePeriod = 15,
@RestoreMode = 0,
@DisconnectUsers = 0,
@RestoreAlertThreshold = 30,
@RestoreAffinity = 0,
@CopyOffset = 100,
@RestoreOffset = 200
Enable Logging
/* this script executes on the publisher. It only logs backup operations. */
exec LogShipping_Update_PrimaryDatabase
@PlanID = N'c2a43886-5da2-40ef-b03d-693ebf185db6',
@BackupLoggingLevel = 2
/* this script executes on subscribers. It only logs restore operations. */
exec LogShipping_Update_Subscriber
@PlanID = N'c2a43886-5da2-40ef-b03d-693ebf185db6',
@RestoreLoggingLevel = 2
Reinitialize Log Shipping
/* this script executes on the publisher: */
exec LogShipping_Update_PrimaryDatabase
@PlanID = 'c2a43886-5da2-40ef-b03d-693ebf185db6'
@PerformBackup = 1
/* this script executes on subscribers: */
exec LogShipping_Sync_Subscriber
@PlanID = 'c2a43886-5da2-40ef-b03d-693ebf185db6'
Remove LiteSpeed Log Shipping Plans
/* Run the following on all servers involved in log shipping: */
exec LogShipping_Remove_Plan
@PlanID = 'c2a43886-5da2-40ef-b03d-693ebf185db6'
Procedures
LiteSpeed uses the following log shipping procedures:
LogShipping_Add_PrimaryDatabase
This stored procedure initializes log shipping on the publisher.
|
Argument |
Description |
|---|---|
|
@AdaptiveCompression |
LiteSpeed automatically selects the optimal compression based on throughput and CPU usage and optimize backups either for size or for speed (default). NOTE: Adaptive Compression is only available with LiteSpeed 6.5 or later; Enterprise license. |
|
@PlanName |
A unique name to identify the plan. |
|
@PlanDescription |
A description of the plan. |
|
@PlanID |
A unique ID to identify the plan. |
|
@Database |
The name of the log shipping primary database (publisher database). |
|
@BackupDirectory |
The backup directory for the publisher. Backup files will stored in this directory. If the default is NULL, files will be backed up to the @BackupShare directory. This parameter must be specified only if @BackupShare directory is located on the same server. The publisher doesn't need network credential to store the file in the shared folder (\\myserver\share) and will use @BackupDirectory (c:\share) instead. |
|
@MirrorBackupDirectory |
The mirror backup directory for the publisher. The backup files will be copied from the original location to this directory. |
|
@BackupRetryAttempts |
The number of times (from 0 through 9999) that the job step is repeated before it is considered to have failed. |
|
@BackupRetryInterval |
The number of minutes (from 1 through 9999) that must pass before the job step is retried. |
|
@BackupShare |
The network path to the backup directory (share directory). |
|
@BackupJobName |
The name of backup job, by default "LiteSpeed Log Shipping Backup - <database> - Plan: <plan name>" |
|
@BackupRetentionPeriod |
The amount of time to store transaction log files. The default is 1440 (24 hours). |
|
@BackupAlertThreshold |
If no transaction log backups have occurred during this amount of time, LiteSpeed displays an alert on the monitoring tab. |
|
@BackupLatencyAlert |
The alert ID. You can specify a different alert ID to perform different actions/notification when different log shipping plans are not in order. The default is 14420 for backup and 14421 for restore. |
|
@BackupLatencyAlertEnabled |
Determines whether the alert will be raised. The default is 1 (true). |
|
@HistoryRetentionPeriod |
The amount of time the plan history will be retained. The default is 1440 (24 hours). |
|
@BackupJobID |
A unique identifier for the backup job. |
|
@Overwrite |
Determines if LiteSpeed will delete the existing log shipping plans. This argument accepts one of the following values:
|
|
@BackupScheduleName |
The name for the backup schedule. The default is "LSBackupSchedule". |
|
@BackupPeriod |
The execution frequency of the backup job. By default, this job runs every 15 minutes. |
|
@BackupMaxCPUUsage |
The maximum CPU usage during transaction log backup. The default value will be used, if this argument is not passed. |
|
@BackupPriority |
Specifies the priority of the LiteSpeed process compared to other processes running on the same server. This argument accepts one of the following values:
The default value will be used, if this argument is not passed. |
|
@BackupBufferCount |
Specifies the number of SQL Server buffers available for a LiteSpeed operation. The default value is set by SQL Server. The default value will be used, if this argument is not passed. |
|
@BackupCompression |
Specifies the compression level for the backup. Valid values are 0 through 8. 0 bypasses the compression routines. The remaining values of 1 through 8 specify compression with increasingly aggressive computation. 2 is the default value for disk backups and 7 is the default value for cloud backups. The default value will be used, if this argument is not passed. |
|
@BackupAffinity |
Specifies the affinity mask for the process. The mask is a 64-bit integer value. By default, it is 0 and will utilize all CPUs. The default value will be used, if this argument is not passed. |
|
@BackupThreads |
Determines the number of threads used for the backup. You will achieve the best results by specifying multiple threads, but the exact value depends on several factors including: processors available, affinity setting, compression level, encryption settings, IO device speed, and SQL Server responsiveness. The default is n-1 threads, where n is the number of processors. The default value will be used, if this argument is not passed. |
|
@BackupMaxTransferSize |
Specifies the largest unit of transfer in bytes to be used between SQL Server and LiteSpeed. The possible values are multiples of 65536 bytes (64 KB) ranging up to 4,194,304 bytes (4 MB). The default is 1048576 (1 MB). |
|
@BackupLoggingLevel |
Specifies the backup logging level. This argument accepts one of the following values:
The default output directory on the Publisher server is C:\Documents and Settings\All Users\Application Data\Quest Software\LiteSpeed\SQL Server\Logs (or C:\ProgramData\Quest Software\LiteSpeed\SQL Server\Logs). |
|
@BackupEncryptionLevel |
Specifies the backup encryption level. The default is 0 (encryption is not in use). |
|
@BackupPassword |
The backup password. The default is NULL. |
|
@PlanEnabled |
The LogShipping plan is enabled. |
|
@PerformBackup |
Determines whether LiteSpeed will perform a full backup of the database to the backup folder. You can use this argument to automatically create a backup on the publisher and restore the backup on the subscriber. |
LogShipping_Add_PrimarySubscriber
Adds a subscriber.
|
Argument |
Description |
|---|---|
|
@PrimaryDatabase |
The name of the log shipping primary database (publisher database). |
|
@SecondaryServer |
The name of the subscriber server. |
|
@SecondaryDatabase |
The name of the log shipping subscriber database. |
LogShipping_Init_Subscriber
Initializes log shipping on the subscriber.
|
Argument |
Description |
|---|---|
|
@PlanEnabled |
The LogShipping plan is enabled. |
|
@PlanName |
A unique name to identify the plan. You must specify either the PlanID or the PlanName to add a server to that plan. |
|
@PlanDescription |
A description of the plan. |
|
@PlanID |
A unique ID to identify the plan. It must be the ID received from the publisher to correctly identify plans on the publisher and subscriber. |
|
@PrimaryServer |
The primary server name. |
|
@PrimaryDatabase |
The name of the log shipping primary database (publisher database). |
|
@BackupSourceDirectory |
The network path to the backup directory (share directory). |
|
@BackupDestinationDirectory |
The destination directory on subscriber where backup files are copied. |
|
@SecondaryDatabase |
The name of the log shipping subscriber database. |
|
@CopyJobName |
The name of the copy job. The default is "LiteSpeed Log Shipping File Copy - <primary database> - Plan: <plan name>". |
| @CopyOffset |
The amount of time by which you want to offset the start time of a job. Use this option to avoid overlap of backup, copy and restore operations. The default copy offset is 100 (1 minute) and the restore offset is 200 (2 minutes). |
|
@RestoreJobName |
The name of the restore job. The default is "LiteSpeed Log Shipping Restore - <primary database> - Plan: <plan name>". |
|
@FileRetentionPeriod |
The amount of time to store transaction log files on the subscriber. The default is 1440 (24 hours). |
|
@CopyScheduleName |
The name for the copy schedule. The default is "LSCopySchedule". |
|
@CopyPeriod |
The execution frequency of the copy job. By default, this job runs every 15 minutes. |
|
@CopyJobID |
A unique identifier for the copy job. |
|
@RestoreScheduleName |
The name for the restore schedule. The default is "LSRestoreSchedule". |
|
@RestorePeriod |
The execution frequency of the restore job. By default, this job runs every 15 minutes. |
| @RestoreOffset |
The amount of time by which you want to offset the start time of a job. Use this option to avoid overlap of backup, copy and restore operations. The default copy offset is 100 (1 minute) and the restore offset is 200 (2 minutes). |
|
@RestoreJobID |
A unique identifier for the restore job. |
|
@SecondaryID |
A unique identifier of the plan ID in native tables. |
|
@RestoreDelay |
The amount of time you want the subscriber database to wait before it restores the transaction log backup. Using this option you can specify how far you want a subscriber to be behind the publisher. By default, load delay is set to 0 minutes, and backups are restored immediately when the restore job runs. |
|
@RestoreAll |
Determines whether all transaction logs are restored. This argument accepts one of the following values:
|
|
@RestoreMode |
Determines the restore mode. This argument accepts one of the following values:
|
|
@DisconnectUsers |
Disconnect users on executing restore (in standby mode only). The default is 0 (users will not be disconnected). |
|
@RestoreAlertThreshold |
If no transaction log restores have occurred during this amount of time, LiteSpeed displays an alert on the monitoring tab. The default alert threshold is 30 minutes. |
|
@RestoreLatencyAlert |
The alert ID. You can specify a different alert ID to perform different actions/notification when different log shipping plans are not in order. The default is 14420 for backup and 14421 for restore. |
|
@RestoreLatencyAlertEnabled |
Determines whether the alert will be raised. The default is 1 (true). |
|
@HistoryRetentionPeriod |
The amount of time the plan history will be retained. The default is 1440 (24 hours). |
|
@Overwrite |
Determines if LiteSpeed will delete the existing log shipping plans. This argument accepts one of the following values:
|
|
@RestorePassword |
The restore password. The default value will be used, if this argument is not passed. |
|
@RestoreAffinity |
Specifies the affinity mask for the process. The mask is a 64-bit integer value. By default, it is 0 and will utilize all CPUs. The default value will be used, if this argument is not passed. |
|
@RestoreLoggingLevel |
Specifies the logging level for restoring backups. This argument accepts one of the following values:
The default output directory on the Publisher server is C:\Documents and Settings\All Users\Application Data\Quest Software\LiteSpeed\SQL Server\Logs (or C:\ProgramData\Quest Software\LiteSpeed\SQL Server\Logs). |
|
@PerformRestore |
Determines whether the subscriber's database will be restored from the backup share folder. This argument accepts one of the following values:
|
|
@RestoreDataDestinationDirectory |
Specifies a directory where the database will be restored. The default is NULL (default SQL Server directory will be used). NOTE: The LogShipping_Init_Subscriber procedure accepts this argument only if @PerformRestore=1. |
|
@RestoreLogDestinationDirectory |
Specifies a directory where the log file will be restored. The default is NULL (default SQL Server directory will be used). NOTE: The LogShipping_Init_Subscriber procedure accepts this argument only if @PerformRestore=1. |
|
@RetryAttempts |
The number of times (from 0 through 9999) that the job step is repeated before it is considered to have failed. |
|
@RetryInterval |
The number of minutes (from 1 through 9999) that must pass before the job step is retried. |
LogShipping_Remove_Plan
Remove a plan from the publisher and subscribers. It must be run on all servers involved in log shipping.
|
Argument |
Description |
|---|---|
|
@PlanID |
A unique ID to identify the plan. |
|
@SubscriberOnly |
Removes information about the subscriber on the server that is the publisher and subscriber at the same time. This argument accepts one of the following values:
|
LogShipping_Remove_PrimarySubscriber
Removes a subscriber.
|
Argument |
Description |
|---|---|
|
@PrimaryDatabase |
The name of the log shipping primary database (publisher database). |
|
@SecondaryServer |
The name of the subscriber server. |
|
@SecondaryDatabase |
The name of the log shipping subscriber database. |
LogShipping_Sync_Subscriber
This stored procedure restores subscriber's database from the backup file in the share directory. Before executing this procedure you must put the backup file in the backup share (manually or using @PerformBackup of the LogShipping_Update_PrimaryDatabase procedure).
|
Argument |
Description |
|---|---|
|
@DisconnectUsers |
Disconnect users on executing restore (in standby mode only). This argument accepts one of the following values:
|
|
@PlanID |
A unique ID to identify the plan. |
LogShipping_Update_PrimaryDatabase
Updates the publisher.
|
Argument |
Description |
|---|---|
|
@PlanName |
A unique name to identify the plan.The default value will be used, if this argument is not passed. |
|
@PlanDescription |
A description of the plan. The default value will be used, if this argument is not passed. |
|
@PlanID |
A unique ID to identify the plan. |
|
@BackupDirectory |
The backup directory for the publisher. Backup files will stored in this directory. If the default is NULL, files will be backed up to the @BackupShare directory. This parameter must be specified only if @BackupShare directory is located on the same server. The publisher doesn't need network credential to store the file in the shared folder (\\myserver\share) and will use @BackupDirectory (c:\share) instead. |
|
@MirrorBackupDirectory |
The mirror backup directory for the publisher. The backup files will be copied from the original location to this directory. |
|
@BackupShare |
The network path to the backup directory (share directory). |
|
@BackupRetentionPeriod |
The amount of time to store transaction log files. The default is 1440 (24 hours). |
|
@BackupAlertThreshold |
If no transaction log backups have occurred during this amount of time, LiteSpeed displays an alert on the monitoring tab. The default alert threshold is 45 minutes. |
|
@BackupLatencyAlert |
The alert ID. You can specify a different alert ID to perform different actions/notification when different log shipping plans are not in order. The default is 14420 for backup and 14421 for restore. |
|
@BackupLatencyAlertEnabled |
Determines whether the alert will be raised. The default is 1 (true). |
|
@HistoryRetentionPeriod |
The amount of time the plan history will be retained. The default is 1440 (24 hours). |
|
@BackupPeriod |
The execution frequency of the backup job. By default, this job runs every 15 minutes. |
|
@BackupMaxCPUUsage |
The maximum CPU usage during transaction log backup. The default value will be used, if this argument is not passed. |
|
@BackupPriority |
Specifies the priority of the LiteSpeed process compared to other processes running on the same server. This argument accepts one of the following values:
|
|
@BackupBufferCount |
Specifies the number of SQL Server buffers available for a LiteSpeed operation. The default value is set by SQL Server. The default value will be used, if this argument is not passed. |
|
@BackupCompression |
Specifies the compression level for the backup. Valid values are 0 through 8. 0 bypasses the compression routines. The remaining values of 1 through 8 specify compression with increasingly aggressive computation. 2 is the default value for disk backups and 7 is the default value for cloud backups. The default value will be used, if this argument is not passed. |
|
@BackupAffinity |
Specifies the affinity mask for the process. The mask is a 64-bit integer value. By default, it is 0 and will utilize all CPUs. The default value will be used, if this argument is not passed. |
|
@BackupThreads |
Determines the number of threads used for the backup. You will achieve the best results by specifying multiple threads, but the exact value depends on several factors including: processors available, affinity setting, compression level, encryption settings, IO device speed, and SQL Server responsiveness. The default is n-1 threads, where n is the number of processors. The default value will be used, if this argument is not passed. |
|
@BackupMaxTransferSize |
Specifies the largest unit of transfer in bytes to be used between SQL Server and LiteSpeed. The possible values are multiples of 65536 bytes (64 KB) ranging up to 4,194,304 bytes (4 MB). The default is 1048576 (1 MB). The default value will be used, if this argument is not passed. |
|
@BackupLoggingLevel |
Specifies the backup logging level. This argument accepts one of the following values:
The default output directory on the Publisher server is C:\Documents and Settings\All Users\Application Data\Quest Software\LiteSpeed\SQL Server\Logs (or C:\ProgramData\Quest Software\LiteSpeed\SQL Server\Logs). |
|
@BackupEncryptionLevel |
Specifies the backup encryption level. The default is 0 (encryption is not in use). |
|
@AdaptiveCompression |
Automatically selects the optimal compression level based on CPU usage or Disk IO. For more information, see Compression Methods. You can tell Adaptive Compression to optimize backups either for size or for speed. This argument accepts one of the following values:
|
|
@BackupRetryAttempts |
The number of times (from 0 through 9999) that the job step is repeated before it is considered to have failed. |
|
@BackupRetryInterval |
The number of minutes (from 1 through 9999) that must pass before the job step is retried. |
|
@PlanEnabled |
The LogShipping plan is enabled. |
|
@PerformBackup |
Determines whether LiteSpeed will perform a full backup of the database to the backup folder. You can use this argument to automatically create a backup on the publisher and restore the backup on the subscriber. |
LogShipping_Update_Subscriber
Updates the subscriber.
|
Argument |
Description |
|---|---|
|
@PlanName |
A unique name to identify the plan. You must specify either the PlanID or the PlanName to add a server to that plan. |
|
@PlanDescription |
A description of the plan. The default is NULL. |
|
@PlanID |
A unique ID to identify the plan. It must be the ID received from the publisher to correctly identify plans on the publisher and subscriber. |
|
@BackupSourceDirectory |
The network path to the backup directory (share directory). |
|
@BackupDestinationDirectory |
The destination directory on subscriber where backup files are copied. |
|
@FileRetentionPeriod |
The amount of time to store transaction log files on the subscriber. The default is 1440 (24 hours). |
|
@CopyPeriod |
The execution frequency of the copy job. By default, this job runs every 15 minutes. |
|
@RestorePeriod |
The execution frequency of the restore job. By default, this job runs every 15 minutes. |
|
@RestoreDelay |
The amount of time you want the subscriber database to wait before it restores the transaction log backup. Using this option you can specify how far you want a subscriber to be behind the publisher. |
|
@RestoreAll |
Determines whether all transaction logs are restored. This argument accepts one of the following values:
|
|
@RestoreMode |
Determines the restore mode. This argument accepts one of the following values:
|
|
@DisconnectUsers |
Disconnect users on executing restore (in standby mode only). The default is 0 (users will not be disconnected). |
|
@RestoreAlertThreshold |
If no transaction log restores have occurred during this amount of time, LiteSpeed displays an alert on the monitoring tab. |
|
@RestoreLatencyAlert |
The alert ID. You can specify a different alert ID to perform different actions/notification when different log shipping plans are not in order. The default is 14420 for backup and 14421 for restore. |
|
@RestoreLatencyAlertEnabled |
Determines whether the alert will be raised. The default is 1 (true). |
|
@HistoryRetentionPeriod |
The amount of time the plan history will be retained. The default is 1440 (24 hours). |
|
@RestoreAffinity |
Specifies the affinity mask for the process. The mask is a 64-bit integer value. By default, it is 0 and will utilize all CPUs. The default value will be used, if this argument is not passed. |
|
@RestoreLoggingLevel |
Specifies the logging level for restoring backups. This argument accepts one of the following values:
The default output directory on the Publisher server is C:\Documents and Settings\All Users\Application Data\Quest Software\LiteSpeed\SQL Server\Logs (or C:\ProgramData\Quest Software\LiteSpeed\SQL Server\Logs). |
|
@RestorePassword |
The restore password. Default is Null. The default value will be used, if this argument is not passed. |
|
@CopyOffset |
Specifies the number of rows to skip when running copy. |
|
@PlanEnabled |
The LogShipping plan is enabled. |
|
@RestoreOffset |
Specifies the number of rows to skip when running a restore. |
|
@RetryAttempts |
The number of times (from 0 through 9999) that the job step is repeated before it is considered to have failed. |
|
@RetryInterval |
The number of minutes (from 1 through 9999) that must pass before the job step is retried. |
Troubleshoot Log Shipping
Review the following solutions to help troubleshoot log shipping issues:
- Synchronize subscribers
- Reinitialize the plan
- Bring the subscriber online
- Reverse log shipping roles
- Manually remove log shipping plan components
- 'Failed to login' error on 64-bit platform
- Adjust copy settings
NOTE: For all log shipping scenarios, the best is to upgrade all of your servers to the latest LiteSpeed version.
Synchronize subscribers
To synchronize subscribers, you will need to create a transaction log backup of the Publisher database and then restore it to the Subscriber. Log shipping plan status is updated immediately after the synchronization is complete.
You may need to synchronize the Publisher and Subscriber in the following scenarios:
-
The Monitoring tab displays the overdue restoring task.
- Some transaction log backup files had been removed from the publisher before they got to the Subscriber or they were manually removed from the Subscriber.
-
Subscriber server goes offline. (This will not stop backups from occurring or stop other subscribers from updating). Synchronize subscriber when the Subscriber server is online.
If the Subscriber is permanently offline you will need to delete the existing plan and create a new log shipping plan
To synchronize the publisher and subscriber databases
Do one of the following:
- Right-click a subscriber database in the Monitoring tab and select Synchronize....
Run the following:
/* On the publisher */
EXEC msdb..sp_start_job @job_id = '<backup_job_id>'
GO/* On the subscribers */
EXEC msdb..sp_start_job @job_id = '<copy_job_id>'
GO/* On the subscribers */
EXEC msdb..sp_start_job @job_id = '<restore_job_id>'
Tip: To obtain job ids, right-click a subscriber database in the Monitoring tab and select Synchronize..., and then click View Script.
Reinitialize the plan
When you reinitialize the plan, LiteSpeed creates a full backup file with the <Publisher database>.bak name in backup share folder and then restores the backup directly to the Subscriber.
You may need to reinitialize log shipping, if the restore job fails.
The restore job may fail in the following cases:
- The job is disabled.
- Backup folder cannot be found.
- Access to shared backup folder is denied.
- LiteSpeed for SQL Server has been removed from the subscriber server.
- The publisher database has been renamed, or removed, or not accessible.
- The t-log was manually backed up on the Publisher and there were database changes.
- Database is not in the "Stand by" or "No Recovery" state.
- Synchronizing subscribers failed.
NOTE: Before you reinitialize log shipping, you need to fix the errors and prevent them from occurring in future.
Right-click the Subscriber database in the Monitoring tab and select Reinitialize.
NOTES: When you reinitialize log shipping, the plan status is updated only after usual backup transaction log, copy and restore jobs are completed.
You can also re-initialize log shipping using stored procedures. For more information, see Reinitialize Log Shipping.
Bring the subscriber online
You may need to bring the Subscriber online, if you have a Log Shipping plan setup and the publisher fails.
To bring the Subscriber online
- Stop any LiteSpeed Log Shipping jobs.
- Run restore database with recovery to bring the subscriber database online.
- Remove the plan and setup the Log Shipping plan again.
Reverse log shipping roles
You may use the following instructions to make a subscriber act as a publisher and vice versa.
To reverse log shipping roles
- Delete the log shipping plan. For more information, see To delete a log shipping plan .
- Restore the subscriber database with recovery.
- Create a new log shipping plan. For more information, see Create Log Shipping Plans.
Manually remove log shipping plan components
For more information, see Remove LiteSpeed Log Shipping Plans.
To remove a native Log Shipping plan on SQL Server 2005 or higher, see http://msdn.microsoft.com/en-us/library/ms191297.aspx.
'Failed to login' error on 64-bit platform
If you use an alias to connect to a SQL Server with non-standard TCP port, the log shipping job may fail on a x64 platform with the 'Failed to login' error.
To workaround this issue
You have to define an alias for both 64-bit and 32-bit server configurations.
Log Shipping utility (SLSLogShip.exe) works as a 32-bit application on x64 platforms and uses x32 alias configuration.
Adjust copy settings
Log Shipping uses robocopy to internally move LiteSpeed files. Log Shipping began using the robocopy tool to copy files from the source to the target since LiteSpeed release 7.5. Robocopy is supported for Windows 7, Windows 8, Windows Server 2008, Windows Server 2008 R2, Windows Server 2012 and above. Beginning with LiteSpeed release 8.0, the robocopy parameters can be changed in the Log Shipping section of the .ini file. The .ini file available values and default values are displayed below. Further information about robocopy is available at: http://technet.microsoft.com/en-us/library/cc733145.aspx.
Available values:
[logshipping]
userobocopy = true/false
numberofretries = decimal
waittimebetweenretries = decimal
numberofthreads = decimal (the parameter applies to Windows Server 2008 R2 and Windows 7 only)
copymode = zb/z/b
Default values:
[logshipping]
userobocopy = true
numberofretries = 60
waittimebetweenretries = 10
numberofthreads = 8
copymode = z
Create Support Bundles
If you have not found an answer to your question, you can create a support bundle and send it to customer support. The support bundle contains information about your database, system configuration, and settings and can help troubleshoot problems.
To create a LiteSpeed UI Console support bundle
Select Application button | Help | Support Bundle.
To create a server support bundle
- Right-click a server in the tree and select Support | LiteSpeed Core Details.
- Send the support bundle to Quest Software customer support or click Clipboard to save the generated content to file.