Log shipping is the process of automatically backing up the transaction logs on a primary database server. Then those logs are restored on to the standby server.

This technique provides the disaster recovery solution for a single primary database. It also provides it for one or more secondary databases. Each database resides on a separate instance of SQL server.

During the interval between restore jobs, we can access secondary databases for read only purpose.

  • For log shipping the recovery model of the database must be full or bulk logged.
  • Collation must be same.
  • We cannot take the backup of secondary database.
  • It does not support automatic failure.
log Shipping block diagram
log Shipping block diagram

Log Shipping Involves below steps

  1. Backup Job – Backup the transaction log file on the primary SQL Server Instance or any backup share.
  2. Copy Job – Copy the transaction log backup file across the network to one or more Secondary SQL server instances
  3. Restore Job – Restore the copied transaction logs on the secondary SQL server instances.

Primary Server

The instance of SQL Server, which is your production server is called as the primary server.

Primary Database

The database on the primary server which you want to backup to another server is called as the primary database.
All the administrative activities are performed from the primary database with the help of SQL Server management studio.

Secondary Database

The worm standby copy of the primary database is called as secondary database.
The secondary database can be in below two states

  1. Recovering
  2. Standby

Monitor server

Monitor Server tracks all the details of this process. It includes below things

  • Keep the information about when the transaction log on the primary database was last backed up.
  • When the secondary server last copied and restored the backup files.
  • Information about any backup failure alert.

Monitor server is an optional.
Note – We cannot make any changes in the monitor server configuration without breaking or removing log shipping configuration.

Operating Modes

There are two operating modes and they are based on the state in which the secondary database will be

  1. Standby mode – The database is available for querying and users can access it, but in read only mode. The database will not
    be available when restore process is running.
  2. Restore mode – The database is not at all accessible

TUF file

TUF stands for “Transaction Undo File”.
This file contains information about any changes. These changes are related to incomplete transactions at the time the backup was done.
It is required if a database is loaded in read state. In this state further transaction log backups may be applied.
It is created while performing log shipping to a server in standby mode.
If TUF file is corrupted or lost, log shipping will not work, and we need to setup it again.

WRK File

The WRK files are produced when the transaction log backups are copied from the backup location to a secondary server.
Once the copy operation completed successfully file automatically renamed to “.trn” file.
It ensures that the files are not picked up by restore job until it successfully copied.

Check all related articles

Posted on 0 By Tushar Kothawade Disaster Recovery Posted in Disaster Recovery Tagged #, , , , ,

Leave a Reply