--------------------------------------------------------------------------------
Manual setup of log shipping using the stored procedures
You must run all stored procedures from the server that will house the publishing database, even if this server is also a subscriber. There are some exceptions to this rule and we will cover those as we get to them. You must also run the stored procedures in a set order. This order is defined by grouping server and database additions by server and you must run the stored procedures for the publisher before you setup any subscribers.
Set up a publisher.
declare @SPlanID as uniqueidentifier
We declare a variable to hold the PlanID output from the SLS_LogShipping_AddPlan.
You can use this going forward or just use the plan name.
SLS_LogShipping_AddPlan is the first procedure. It accepts several variables defined in line above and detailed in appendix ALS Store Procedures.
exec SLS_LogShipping_AddPlan
'TestPlan'
-
name of the log shipping plan
, 'TestDiscription'
-
brief description of the log shipping plan
, 'server1'
-
server that will be participating in this plan
, 30
-
only used if you are encrypting backups.
, 4
-
number of hours to keep log files on disk after they have been restored to subscribers
, 60
-
number of minutes of latency allowed in the backup job and restore jobs
, 'True'
-
used a trusted windows connection for all processes
, 'SQLUser'
-
only used if the above is set to False this account must have SysAdmin privileges
, 'SQLPassword'
-
only used if the above is set to a valid SQL account.
, @EncryptionPassword
-
only used if you are encrypting backups.
, @EncryptionLevel
-
only used if you are encrypting backups.
, 'LiteSpeedLocal'
-
control database for all log shipping tables and procedures
, @SPlanID output
-
output variable
, 1
-
set logging on for this procedure and the plan as a whole
SLS_LogShipping_AddServer is the second procedure. It accepts several variables defined in line above and detailed in appendix ALS Store Procedures.
exec SLS_LogShipping_AddServer
@SPlanID
-
you must provide a PlanID or Plan Name below
,NULL
-
plan name can be NULL if the above has the PlanID
,'server1'
-
name of the server to Add
,1
-
destination type for the server 0 = UNC
SLS_LogShipping_AddServerPlan is the third procedure. It accepts several variables defined in line above and detailed in appendix ALS Store Procedures. Destination type does not affect the publisher set to default of 0.
exec SLS_LogShipping_AddServerPlan
@SPlanID
-
You must provide a PlanID or Plan Name below
,NULL
-
Plan name can be NULL if the above has the PlanID
,'server1'
-
Name of the server to Add
,0
-
Server role 0 = publisher, 1 = subscriber
,0
-
Destination type for the server 0 = UNC
SLS_LogShipping_AddDatabase is the fourth proceduren. It accepts several variables defined in line above and detailed in appendix ALS Store Procedures.
exec SLS_LogShipping_AddDatabase
@SPlanID
-
You must provide a PlanID or Plan Name below
,NULL
-
Plan name can be NULL if the above has the PlanID
,'Northwind'
-
Name of database to add
,'server1'
-
Name of the server to add
SLS_LogShipping_AddDatabasePlan is the fifth procedure. It accepts several variables defined in line above and detailed in appendix ALS Store Procedures. Load Delay does not affect the publisher set to default of 0. Recovery Mode does not affect the publisher set as default of 0.
exec SLS_LogShipping_AddDatabasePlan
@SPlanID
-
You must provide a PlanID or Plan Name below
,NULL
-
Plan name can be NULL if the above has the PlanID
,'server1'
-
Name of the server to Add
,'Northwind'
-
Name of database to add
,1
-
Database role 0 = publisher, 1 = subscriber
,0
-
Load Delay in hours (has no effect if this is the publishing database)
,1
-
Recovery mode of database 0 = Loading , 1=Read only (has no effect if this is the publishing database)
Now that we have our publisher set up we can start adding subscribers.
We run the same set of procedures again changing the server name, database name, and role where needed. Remember these procedures must be executed from the publishing server.
Once the publisher and all subscriber databases have been added we can move to adding destinations for UNC.
Exec SLS_LogShipping_AddUNCDestination
@SPlanID
-
You must provide a PlanID or Plan Name below
,NULL
-
Plan name can be NULL if the above has the PlanID
,'c:\logshippingbackups'
-
local folder files are copied to
,'\\fileserver\logshipping'
-
remote UNC share files are stored on
The next step is to add the jobs to each server that will actually perform the scheduled operations. Remember, all stored procedures must be executed from the publisher server.
SLS_LogShipping_Add_Backup_Job, SLS_LogShipping_Add_Backup_Alert_Job, SLS_LogShipping_Add_Cleanup_Job, SLS_Add_Restore_Job, SLS_Add_Restore_Alert_Job are executed on the publisher server.
Exec SLS_LogShipping_Add_Backup_Job
@SPlanID
-
You must provide a PlanID or Plan Name below
,NULL
-
Plan name can be NULL if the above has the PlanID
Exec SLS_LogShipping_Add_Backup_Alert_Job
@SPlanID
-
You must provide a PlanID or Plan Name below
,NULL
-
Plan name can be NULL if the above has the PlanID
Exec SLS_LogShipping_Add_Cleanup_Job
@SPlanID
-
You must provide a PlanID or Plan Name below
,NULL
-
Plan name can be NULL if the above has the PlanID
Exec SLS_LogShipping_Add_Restore_Job
@SPlanID
-
You must provide a PlanID or Plan Name below
,NULL
-
Plan name can be NULL if the above has the PlanID
Exec SLS_LogShipping_Add_Restore_Alert_Job
@SPlanID
-
You must provide a PlanID or Plan Name below
,NULL
-
Plan name can be NULL if the above has the PlanID
Now that the plans are in place there are a few procedures to help you manage these plans.
You will need to initialize the plan by calling SLS_Init_LogShipping. This procedure is also used to re-initialize a plan if needed:
SLS_Init_LogShipping
‘PlanID'
-
you must specify either the PlanID or the PlanName to add a UNC destination to that plan
‘TestPlan'
-
you must specify either the PlanID or the PlanName to add a UNC destination to that plan
,1
-
Initialize the database 0 = do not perform full backup, 1= perform full backup as part of the init
This is to give flexibility to those who wish to perform their own full backup and not have it managed as part of the log shipping plan.
If you need to change the passwords for the SQL user account you can execute SLS_Reset_Passwords:
SLS_Reset_Passwords
‘PlanID'
-
you must specify either the PlanID or the PlanName to add a UNC destination to that plan
‘TestPlan'
-
you must specify either the PlanID or the PlanName to add a UNC destination to that plan
,0
-
passsword type. 0 = SQL
,'Password'
-
the new password you wish the plan to have
If you should need to troubleshoot a plan you can execute SLS_Set_Logging_Plan. This turns logging on or off for a specific plan. You can turn logging off or on when you add the plan this allows to toggle it on later for troubleshooting purposes. This will also turn on LiteSpeed logging and generate the standard LiteSpeed log files:
SLS_Set_Logging_Plan
‘PlanID'
-
you must specify either the PlanID or the PlanName to add a UNC destination to that plan
‘TestPlan'
-
you must specify either the PlanID or the PlanName to add a UNC destination to that plan
,0
-
error logging. 0 = off, 1 = on
If you wish to remove a plan all together you can execute SLS_Remove_Plan. This will remove a plan completely. All jobs and records are deleted, backup files ARE NOT REMOVED!!!!
SLS_Remove_Plan
‘PlanID'
-
you must specify either the PlanID or the PlanName to add a UNC destination to that plan
‘TestPlan'
-
you must specify either the PlanID or the PlanName to add a UNC destination to that plan
--------------------------------------------------------------------------------
© 2025 Quest Software Inc. ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center
