How to configure Log Shipping using two instances Step by Step

Log Shipping

The backup compression feature was introduced in SQL Server 2008 Enterprise edition.

Configuration of Log shipping involves 3 jobs they are:

Log Backup job:

Log backups of the database are taken at regular intervals according to the specified and are saved in the Source folder of the Primary Instance/Server.

Copy Job:

This job helps in copying the log backup files from source folder to the destination folder of the Secondary Instance/Server according to the specified schedule.

Restore Job:

This job helps in restoring the log backups from the destination folder in to database of the Secondary Instance/Server.

Steps to Configure Log-Shipping:

Step-1: Make sure that your database is in full or bulk-logged recovery model. 

To do this Right click on the respective database ---> Go to options ---> Select Full from the drop down list of Recovery Model.

Step-2: On the primary server(primary instance), 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.

Step-3: The next step is to configure and schedule a transaction log backup. 

Click on Backup Settings... to do this.

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. 

For network path right click on the properties of the folder where we are storing our log backups ---> Click to Sharing tab ---> you will find a network path for the respective backup folder.

Enter the local path of the folder. Here a backup job is created.

When you click on edit jobs you can find the details of the job and its schedule.

Click OK to Continue.

Step-4: Click on Add

Step-5: Connect to secondary server instance.

Step-6: In the initialize Secondary Database tab check the first radio button according to the requirement.

In this a full database backup of the primary database is taken and is restored automatically.If the database does not exist it creates an own database with the same name as the primary database.

Step-7: Give the Destination folder path for the log backups to be copied in the secondary Instance\Server by the “LSCopy_School” Job.

Step-8: Click On Schedule. Here you can find the details of the Job and its Schedule.

Step-9: Click on Restore Transaction Log and select standby mode and check Disconnect users in the database when restoring backups. This restoring process is done by the “LSRestore_School2” Job.
Click on Ok to continue.

Step-10: Click on OK.

Step-11: If all the actions are successful click on close.

Step-12: Connect to Secondary instance.

Step-13: You can find the same School database in the Secondary instance/Server in Standby/Read-Only mode.

Step-14: Insert a record in the Primary database as below.

Step-15: Connect to secondary instance and query the Course table in the School database.
You can find the newly inserted record in the Secondary database.
Total time taken to ship the logs depends on the log backup+copy+restore jobs.