Create Log Shipping in SQL Server

How to Create Log Shipping in SQL Server

Log shipping is perhaps the most common method of providing high availability. The basic idea is that the transaction log, with its record of the most recent transactions, is regularly backed up and then sent, or shipped, to another server where the log is applied so that server has a pretty fresh copy of all the data from the primary server. Log shipping doesn’t require any special hardware or magic, and it’s relatively easy to set up and administer.

 

 

Steps to create Logshipping:

Servers involved:

Instance 1: Mycomputer

Instance 2: MyComputer\server1

Database : Room

Activity: Log shipping to configure for "ROOM" database from "Mycomputer" Instance to "Mycomputer\server1" Instance.

  
Explanation:

Righclick the database to be involved in Log shipping, Enable the check box highlighed in the pop up window shown above. After enabling the check box, click the "Backup Settings" button.

Explanation:

Give the shared location path where backup files should be placed.
Click the schedule option for changing the properties of the backup job that is going to get created in the AGENT. Pop window is showing that job run time and all.

Explanation:

After completing the backup job settings, Add the secondary server details by clicking the "Add" button as shown. Connect to secondary server.

Explanation:

Create the full backup of the primary database and move it to secondary server if it doesn't exist. Click the "Restore Options" button to modify the job as shown below.

Explanation:

Copy Job is changed as per the need shown above.

Explanation:

Give the path where copied files (backup files from Primary server) should be palced in Destination server. Change the job settings as per the need. Move to next tab, "Restore Transaction Log" in the next screenshot.

Explanation:

Secondary database can be of two modes, one is "No recovery Mode" or "Standby mode".
Change the restore job as per our need.

After configuring all the options, click ok to proceed. Log shipping will get configured from Primary server to secondary server.

By following above steps, Logshipping is configured from one serve to another.

 

 

In the above process we have created secondary database in "Standby Mode". In this a file is created in Destination secondary server called TUF file.

TUF file is a Microsoft SQL Server Transaction Undo file. .TUF File contains the information regarding any modifications that were made as part of incomplete transactions at the time the backup was performed.A transaction undo(.TUF) file is required if a database is loaded in read-only state. In this state, further transaction log backups may be applied.


Hope the above information helps

Thanks.