SQL Server Log Shipping

SQL Server log shipping is one of the high availability solutions which involves two or more SQL Server instances; the source server is called the Primary Server and the destination is called Secondary server(s).  Log shipping involves the transferring of the transaction log file from the primary server to the secondary server.  Log Shipping is a basic level SQL Server high-availability technology that is part of SQL Server.  This is automated by three SQL jobs

Advantages are:

  • SQL Server log shipping is primarily used as a disaster recovery solution
  • It’s reliable and tested in details
  • It’s easy to set up and maintain
  • Log shipping can be combined with other disaster recovery options such as Always On Availability Groups, database mirroring, and database replication
  • Low cost in human and server resources

Disadvantages are:

  • Need to manage all the databases separately
  • There isn’t possibility for an automatic failover; must be manual
  • And secondary database isn’t fully readable while the restore process is running

An SQL Server that tracks all of the details of log shipping such as:

  • Transaction log last backed up
  • Copied and restored the backup files on secondary server
  • Information about backup failure alerts

Prerequisite: the Primary Server should be in Full Recovery Mode

--FIND THE RECOVERY MODE

SELECT name AS [Database Name],
recovery_model_desc AS [Recovery Model] FROM sys.databases
GO

--CHANGE THE RECOVERY MODE IF NECESSARY

USE [master]
GO

ALTER DATABASE LOGSHIP 
SET RECOVERY FULL 
WITH NO_WAIT
GO

Operating modes:

There are two available modes and they are related to the state in which the secondary log shipped SQL Server database will be:

  • Standby mode – the database is available for querying and users can access it, but in read-only mode
  • Restore mode – the database is not accessible

STEP BY STEPS:

  • Create a domain user account for log shipping
  • Create a Sql login for log shipping with sysadmin permission
  • Create a domain service account for Sql Server and Sql agent
  • Create a network share folder for the backups on primary with permission to Sql agent (read)
  • Create a network share folder for the copy/restore on secondary with permission to Sql agent (read and write)
  • Find recovery mode of log shipping database
  • Take full back up of the primary database to network share
  • Restore full and log backup on secondary server with no recovery option (restoring state)
  • Set up log shipping via gui on primary database
  • Copy log shipping via gui in secondary server
  • Restore log shipping via gui in secondary server
  • Check the log shipping process with data

Permissions

Must have sysadmin rights on the server

--ON PRIMARY SERVER!!!

use master
go

create database LogShipping
go

use LogShipping
go

create table cars
(carid int identity (1,1)primary key,
carname varchar (25))


insert into cars
values ('Porche'),('BMW')

select * from cars


--LOG SHIPPING
--TO EXECUTE LOG SHIPPING , THE RECOVERY MODE MUST BE ST TO FULL

SELECT name AS [Database Name],
recovery_model_desc AS [Recovery Model] 
FROM sys.databases
GO


-- IF ITS SIMPLE, THEN CHANGE TO FULL

USE [master]
GO

ALTER DATABASE logshipping 
SET RECOVERY FULL 
GO

BACKUP DATABASE [LogShipping] 
TO  DISK = N'\\DESKTOP-QMOOH4U\backuplogshipping\logshipping.bak' 
WITH NOFORMAT, 
NOINIT,  
NAME = N'LogShipping-Full Database Backup', 
SKIP, 
NOREWIND, 
NOUNLOAD,  
STATS = 10
GO


BACKUP LOG [LogShipping] 
TO  DISK = N'\\DESKTOP-QMOOH4U\backuplogshipping\logshipping.trn' 
WITH NOFORMAT, 
NOINIT,  
NAME = N'LogShipping-Log Database Backup', 
SKIP, 
NOREWIND, 
NOUNLOAD,  
STATS = 10
GO


---ON SECONDARY SERVER RESTORE DATABASE  (CHANGE CONNECTION TO SECONDARY QUERY PANE)

USE [master]
RESTORE DATABASE [LogShipping] 
FROM  DISK = N'C:\backuplogshipping\logshipping.bak' 
WITH  FILE = 1,  
MOVE N'LogShipping' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.DEV\MSSQL\DATA\LogShipping.mdf',  
MOVE N'LogShipping_log' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.DEV\MSSQL\DATA\LogShipping_log.ldf',  
NORECOVERY,  
NOUNLOAD,  
REPLACE,  
STATS = 5

RESTORE LOG [LogShipping] 
FROM  DISK = N'C:\backuplogshipping\logshipping.trn' 
WITH  FILE = 1,  
STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL12.DEV\MSSQL\Backup\LogShipping_RollbackUndo_2016-03-13_18-25-18.bak',  
NOUNLOAD,  
STATS = 5

GO

--SELECT COMMAND SHOWS 2 RECORDS BECAUSE THE JOBS ARE SET FOR 15 MINS (MANUAL RUN WILL CAUSE THE THIRD WO TO BE ADDED)

USE LogShipping
GO

select * from cars

insert into cars
values ('BENZ')

Steps to Configure SQL Server Log Shipping

Step 1

Make sure your database is in full or bulk-logged recovery model. You can change the database recovery model using the below query. You can check the database recovery model by querying sys.databases

SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'jugal'
	
USE [master]
GO
ALTER DATABASE [jugal] SET RECOVERY FULL WITH NO_WAIT
GO

Step 2

On the primary server, right click on the database in SSMS and select Properties. Then select the Transaction Log Shipping Page. Check the “Enable this as primary database in a log shipping configuration” check box.

enable this as a primary database in a log shipping configuration

Step 3

The next step is to configure and schedule a transaction log backup. Click on Backup Settings… to do this.

log shipping transaction log backups

If you are creating backups on a network share enter the network path or for the local machine you can specify the local folder path. The backup compression feature was introduced in SQL Server 2008 edition. While configuring log shipping, we can control the backup compression behavior of log backups by specifying the compression option. When this step is completed it will create the backup job on the Primary Server.

log shipping transaction log backup settings

Step 4

In this step we will configure the secondary instance and database. Click on the Add… button to configure the Secondary Server instance and database. You can add multiple servers if you want to setup one to many server log-shipping.

On the primary server, right click on the database in SSMS and select Properties. Then select the Transaction Log Shipping Page. Check the “Enable this as primary database in a log shipping configuration” check box.

enable this as a primary database in a log shipping configuration

Step 3

The next step is to configure and schedule a transaction log backup. Click on Backup Settings… to do this.

log shipping transaction log backups

If you are creating backups on a network share enter the network path or for the local machine you can specify the local folder path. The backup compression feature was introduced in SQL Server 2008 edition. While configuring log shipping, we can control the backup compression behavior of log backups by specifying the compression option. When this step is completed it will create the backup job on the Primary Server.

log shipping transaction log backup settings

Step 4

In this step we will configure the secondary instance and database. Click on the Add… button to configure the Secondary Server instance and database. You can add multiple servers if you want to setup one to many server log-shipping.https://e85f02d7c3dad118af06f228d566e301.safeframe.googlesyndication.com/safeframe/1-0-37/html/container.html

add a secondary server for log shipping

When you click the Add… button it will take you to the below screen where you have to configure the Secondary Server and database. Click on the Connect… button to connect to the secondary server. Once you connect to the secondary server you can access the three tabs as shown below.

Initialize Secondary Database for Log Shipping on SQL Server

In this step you can specify how to create the data on the secondary server. You have three options: create a backup and restore it, use an existing backup and restore or do nothing because you have manually restored the database and have put it into the correct state to receive additional backups.

log shipping secondary database initialize secondary database settings

Copy Files for Log Shipping for SQL Server

In this tab you have to specify the path of the Destination Shared Folder where the Log Shipping Copy job will copy the T-Log backup files. This step will create the Copy job on the secondary server.

log shipping secondary database restore copy files settings

Restore Transaction Log for SQL Server Log Shipping

Here you have to specify the database restoring state information and restore schedule. This will create the restore job on the secondary server.

log shipping secondary database settings

Step 5

In this step we will configure Log Shipping Monitoring which will notify us in case of any failure. Please note Log Shipping monitoring configuration is optional.

monitor sql server instance

Click on Settings… button which will take you to the “Log Shipping Monitor Settings” screen. Click on Connect … button to setup a monitor server. Monitoring can be done from the source server, target server or a separate SQL Server instance. We can configure alerts on source / destination server if respective jobs fail. Lastly we can also configure how long job history records are retained in the MSDB database. Please note that you cannot add a monitor instance once log shipping is configured.

log shipping monitor settings

Step 6

Click on the OK button to finish the Log Shipping configuration and it will show you the below screen.

save log shipping configuration

Also See:

Configure Log Shipping