title	Configure log shipping for SQL Server on Linux \| Microsoft Docs
description	This tutorial shows a basic example of how to replicate a SQL Server instance on Linux to a secondary instance using log shipping.
author	meet-bhagdev
ms.author	meetb
manager	craigg
ms.date	04/19/2017
ms.topic	article
ms.prod	sql-non-specified
ms.prod_service	database-engine
ms.service
ms.component
ms.suite	sql
ms.custom	sql-linux
ms.technology	database-engine
ms.assetid
ms.workload	Inactive

Get started with Log Shipping on Linux

[!INCLUDEappliesto-ss-xxxx-xxxx-xxx-md-linuxonly]

SQL Server Log shipping is a HA configuration where a database from a primary server is replicated onto one or more secondary servers. In a nutshell, a backup of the source database is restored onto the secondary server. Then the primary server creates transaction log backups periodically, and the secondary servers restore them, updating the secondary copy of the database.

As described in the this picture, a log shipping session involves the following steps:

Backing up the transaction log file on the primary SQL Server instance
Copying the transaction log backup file across the network to one or more secondary SQL Server instances
Restoring the transaction log backup file on the secondary SQL Server instances

Prerequisites

Install SQL Server Agent on Linux

Setup a network share for Log Shipping using CIFS

Note

This tutorial uses CIFS + Samba to setup the network share. If you want to use NFS, leave a comment and we will add it to the doc.

Configure Primary Server

Run the following to install Samba

sudo apt-get install samba #For Ubuntu
sudo yum -y install samba #For RHEL/CentOS

Create a directory to store the logs for Log Shipping and give mssql the required permissions

mkdir /var/opt/mssql/tlogs
chown mssql:mssql /var/opt/mssql/tlogs
chmod 0700 /var/opt/mssql/tlogs

Edit the /etc/samba/smb.conf file (you need root permissions for that) and add the following section:
```
[tlogs]
path=/var/opt/mssql/tlogs
available=yes
read only=yes
browsable=yes
public=yes
writable=no
```
Create a mssql user for Samba
```
sudo smbpasswd -a mssql
```

Restart the Samba services

sudo systemctl restart smbd.service nmbd.service

Configure Secondary Server

Run the following to install the CIFS client

sudo apt-get install cifs-utils #For Ubuntu
sudo yum -y install cifs-utils #For RHEL/CentOS

Create a file to store your credentials. Use the password you recently set for your mssql Samba account

vim /var/opt/mssql/.tlogcreds
#Paste the following in .tlogcreds
username=mssql
domain=<domain>
password=<password>

Run the following commands to create an empty directory for mounting and set permission and ownership correctly

mkdir /var/opt/mssql/tlogs
sudo chown root:root /var/opt/mssql/tlogs
sudo chmod 0550 /var/opt/mssql/tlogs
sudo chown root:root /var/opt/mssql/.tlogcreds
sudo chmod 0660 /var/opt/mssql/.tlogcreds

Add the line to etc/fstab to persist the share

//<ip_address_of_primary_server>/tlogs /var/opt/mssql/tlogs cifs credentials=/var/opt/mssql/.tlogcreds,ro,uid=mssql,gid=mssql 0 0

Mount the shares
```
sudo mount -a
```

Setup Log Shipping via T-SQL

Run this script from your primary server

BACKUP DATABASE SampleDB
TO DISK = '/var/opt/mssql/tlogs/SampleDB.bak'
GO

DECLARE @LS_BackupJobId	AS uniqueidentifier 
DECLARE @LS_PrimaryId	AS uniqueidentifier 
DECLARE @SP_Add_RetCode	As int 
EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database 
         @database = N'SampleDB' 
        ,@backup_directory = N'/var/opt/mssql/tlogs' 
        ,@backup_share = N'/var/opt/mssql/tlogs' 
        ,@backup_job_name = N'LSBackup_SampleDB' 
        ,@backup_retention_period = 4320
        ,@backup_compression = 2
        ,@backup_threshold = 60 
        ,@threshold_alert_enabled = 1
        ,@history_retention_period = 5760 
        ,@backup_job_id = @LS_BackupJobId OUTPUT 
        ,@primary_id = @LS_PrimaryId OUTPUT 
        ,@overwrite = 1 

IF (@@ERROR = 0 AND @SP_Add_RetCode = 0) 
BEGIN 

DECLARE @LS_BackUpScheduleUID	As uniqueidentifier 
DECLARE @LS_BackUpScheduleID	AS int 

EXEC msdb.dbo.sp_add_schedule 
        @schedule_name =N'LSBackupSchedule' 
        ,@enabled = 1 
        ,@freq_type = 4 
        ,@freq_interval = 1 
        ,@freq_subday_type = 4 
        ,@freq_subday_interval = 15 
        ,@freq_recurrence_factor = 0 
        ,@active_start_date = 20170418 
        ,@active_end_date = 99991231 
        ,@active_start_time = 0 
        ,@active_end_time = 235900 
        ,@schedule_uid = @LS_BackUpScheduleUID OUTPUT 
        ,@schedule_id = @LS_BackUpScheduleID OUTPUT 

EXEC msdb.dbo.sp_attach_schedule 
        @job_id = @LS_BackupJobId 
        ,@schedule_id = @LS_BackUpScheduleID  

EXEC msdb.dbo.sp_update_job 
        @job_id = @LS_BackupJobId 
        ,@enabled = 1 
        
END 

EXEC master.dbo.sp_add_log_shipping_alert_job 

EXEC master.dbo.sp_add_log_shipping_primary_secondary 
        @primary_database = N'SampleDB' 
        ,@secondary_server = N'<ip_address_of_secondary_server>' 
        ,@secondary_database = N'SampleDB' 
        ,@overwrite = 1

Run this script from your secondary server

RESTORE DATABASE SampleDB FROM DISK = '/var/opt/mssql/tlogs/SampleDB.bak'
WITH NORECOVERY;

DECLARE @LS_Secondary__CopyJobId	AS uniqueidentifier 
DECLARE @LS_Secondary__RestoreJobId	AS uniqueidentifier 
DECLARE @LS_Secondary__SecondaryId	AS uniqueidentifier 
DECLARE @LS_Add_RetCode	As int 

EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary 
        @primary_server = N'<ip_address_of_primary_server>' 
        ,@primary_database = N'SampleDB' 
        ,@backup_source_directory = N'/var/opt/mssql/tlogs/' 
        ,@backup_destination_directory = N'/var/opt/mssql/tlogs/' 
        ,@copy_job_name = N'LSCopy_SampleDB' 
        ,@restore_job_name = N'LSRestore_SampleDB' 
        ,@file_retention_period = 4320 
        ,@overwrite = 1 
        ,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT 
        ,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT 
        ,@secondary_id = @LS_Secondary__SecondaryId OUTPUT 

IF (@@ERROR = 0 AND @LS_Add_RetCode = 0) 
BEGIN 

DECLARE @LS_SecondaryCopyJobScheduleUID	As uniqueidentifier 
DECLARE @LS_SecondaryCopyJobScheduleID	AS int 

EXEC msdb.dbo.sp_add_schedule 
        @schedule_name =N'DefaultCopyJobSchedule' 
        ,@enabled = 1 
        ,@freq_type = 4 
        ,@freq_interval = 1 
        ,@freq_subday_type = 4 
        ,@freq_subday_interval = 15 
        ,@freq_recurrence_factor = 0 
        ,@active_start_date = 20170418 
        ,@active_end_date = 99991231 
        ,@active_start_time = 0 
        ,@active_end_time = 235900 
        ,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT 
        ,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT 

EXEC msdb.dbo.sp_attach_schedule 
        @job_id = @LS_Secondary__CopyJobId 
        ,@schedule_id = @LS_SecondaryCopyJobScheduleID  

DECLARE @LS_SecondaryRestoreJobScheduleUID	As uniqueidentifier 
DECLARE @LS_SecondaryRestoreJobScheduleID	AS int 

EXEC msdb.dbo.sp_add_schedule 
        @schedule_name =N'DefaultRestoreJobSchedule' 
        ,@enabled = 1 
        ,@freq_type = 4 
        ,@freq_interval = 1 
        ,@freq_subday_type = 4 
        ,@freq_subday_interval = 15 
        ,@freq_recurrence_factor = 0 
        ,@active_start_date = 20170418 
        ,@active_end_date = 99991231 
        ,@active_start_time = 0 
        ,@active_end_time = 235900 
        ,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT 
        ,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT 

EXEC msdb.dbo.sp_attach_schedule 
        @job_id = @LS_Secondary__RestoreJobId 
        ,@schedule_id = @LS_SecondaryRestoreJobScheduleID  
        
END 
DECLARE @LS_Add_RetCode2	As int 
IF (@@ERROR = 0 AND @LS_Add_RetCode = 0) 
BEGIN 

EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database 
        @secondary_database = N'SampleDB' 
        ,@primary_server = N'<ip_address_of_primary_server>' 
        ,@primary_database = N'SampleDB' 
        ,@restore_delay = 0 
        ,@restore_mode = 0 
        ,@disconnect_users	= 0 
        ,@restore_threshold = 45   
        ,@threshold_alert_enabled = 1 
        ,@history_retention_period	= 5760 
        ,@overwrite = 1 

END 

IF (@@error = 0 AND @LS_Add_RetCode = 0) 
BEGIN 

EXEC msdb.dbo.sp_update_job 
        @job_id = @LS_Secondary__CopyJobId 
        ,@enabled = 1 

EXEC msdb.dbo.sp_update_job 
        @job_id = @LS_Secondary__RestoreJobId 
        ,@enabled = 1 

END

Verify Log Shipping works

Verify that Log Shipping works by starting the following job on the primary server

USE msdb ;  
GO  

EXEC dbo.sp_start_job N'LSBackup_SampleDB' ;  
GO

Verify that Log Shipping works by starting the following job on the secondary server

USE msdb ;  
GO  

EXEC dbo.sp_start_job N'LSCopy_SampleDB' ;  
GO  
EXEC dbo.sp_start_job N'LSRestore_SampleDB' ;  
GO  
RESTORE DATABASE SampleDB WITH RECOVERY;

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Get started with Log Shipping on Linux

Prerequisites

Setup a network share for Log Shipping using CIFS

Configure Primary Server

Configure Secondary Server

Setup Log Shipping via T-SQL

Verify Log Shipping works

FilesExpand file tree

sql-server-linux-use-log-shipping.md

Latest commit

History

sql-server-linux-use-log-shipping.md

File metadata and controls

Get started with Log Shipping on Linux

Prerequisites

Setup a network share for Log Shipping using CIFS

Configure Primary Server

Configure Secondary Server

Setup Log Shipping via T-SQL

Verify Log Shipping works