SQL Server Log Shipping Step By Step – Detailed Guide

Any damage that can result in data loss in the SQL Server is a nightmare for the database administrators. To resolve this issue, we will discuss SQL Server Log Shipping step by step to make it easier for the users to secure their database more efficiently. But as many of us might not be aware of what log shipping is, we will begin by understanding the concept first. 

What is SQL Server Log Shipping?

If we talk simply about what log shipping is, it can be defined as a disaster recovery method for the SQL Server. The log shipping in SQL Server is a feature that allows the users to store the SQL database backups on different SQL Servers. This feature majorly helps the users to keep a copy of the database by constantly shipping the backups of transaction logs. When the users encounter any situation where they are at a risk of losing data, this feature helps them to recover the database in a more professional manner. 

Let’s take a deeper look at how this feature works:

The MS SQL Server log shipping allows the users to take a backup of the transaction logs from the primary database on the primary server, and then send them to secondary databases (one or more), on the secondary servers. This way the users create a copy of their primary databases on secondary servers and secure their data in case of any unexpected events.

But this is only a theoretical definition. How about we take a look at the implementation of log shipping to understand the method in a detailed way. 

SQL Server Log Shipping Step By Step – Explained

For successful MS SQL Server log shipping, there are a few prerequisites and steps that are necessary to be followed. With the accurate execution of steps, it becomes more convenient for the users to configure log shipping in SQL Server. Let’s now take a look at what these prerequisites are. 

Prerequisites For Log Shipping Configuration in SQL

• The version of SQL Server must be 2005 or later versions. 
• All the SQL Server instances must have the Workgroup, Standard or the Enterprise editions installed. 
• All the SQL Servers being used in log shipping must have the same case sensitivity settings.
• Full recovery or Bulk-Logged recovery model is a must for log shipping in SQL Server.
• To copy T-Log Backups, a shared folder is required. 
• Proper configuration of SQL Server Agent must be done.

Remember: It is required to use the same SQL Server version on both ends as it might create problems while using different versions.  

All these requirements are necessary to be met for successful log shipping in SQL. 

Next, let’s learn more about SQL Server log shipping step by step for better understanding. 

Steps for the Configuration of SQL Server Log Shipping

1. The first step is to open the SQL Server Management Studio. Then connect to the Primary SQL Server Instance.
   
2. Then select the database for log shipping and Right-Click on it. From the properties, go to backup options to ensure the recovery model is either Full or Bulk-Logged.
   
   
3. Next, from properties, select the Transaction Log Shipping Option. To enable the log shipping option, click on the checkbox in front of the option.
   
   
4. To specify the location where the transaction log backups will be stored, enter the network and the local path to the primary server database.
   
   
5. Next, an option to schedule the backup is also available. Users can schedule the backups as per their preferences.
   
6. Now, to add the secondary server for log shipping, click on the Add button. With the help of this option, you can add multiple secondary servers. Then, you can add a third instance as a monitor instance to keep track of the shipping process.
   
7. Then, a window will show up for the connectivity of the second server instance. Add the credentials of the second server, and the options for server initialization will show up.
   
8. Here you will see three options.
   1. Initialize Secondary database: Offers two options to either to generate a full backup or to restore from an existing backup.
   2. Copy: In the copy files option, we will specify the destination folder. The transaction log backup will copy to the specified destination.
   3. Restore Transaction Log: In the restore tab, you get two options: the no recovery mode and the standby mode.
9. After adding the other required details like the scheduled time and job name, click on the OK button. You will get a shipping successful message if the process completes without any errors.

Also Read: What is SQL Server High Availability and Disaster Recovery? Complete Guide

Using T-SQL Command For SQL Server Log Shipping

For the configuration of the log shipping using the transaction SQL method, follow the given steps:

1. The first step for this method is to initialize the secondary database with a full recovery backup of the primary database. 
2. Next, in the primary server, add a schedule for the backup by using the command :  sp_add_log_shipping_primary_database.
3. Then, enter the command: sp_add_jobschedule, to add the schedule in the primary server.
4. And next use the sp_add_log_shipping_alert_job to add the job alert in the monitor instance.
5. Next, enable the backup job on the primary server. 
6. Now with the help of the sp_add_log_shipping_secondary_primary, to move the details of the primary database. 
7. Use the sp_add_jobschedule command again to schedule the restore job.
8. To add a secondary database on the secondary server, use the command: sp_add_log_shipping_secondary_database. 
9. To copy the primary server database to the new secondary database, enter the command: sp_add_log_shipping_primary_secondary. 
10. Then, enable the restore job in the secondary server and database. 

By following these steps, one can easily configure the log shipping in SQL Server. 

Let’s now understand what are the components and the benefits of SQL Server Log Shipping.

Components Used in Log Shipping Configuration

• Primary Server: The  primary SQL Server database a user is working on.
• Primary Database: The database present in the primary server that needs to be backed up. The initial steps for the log shipping in SQL Server are done on the primary database.   
• Secondary Server: The server used to store the backup of the primary database. 
• Secondary Database: The database on the secondary server that will be used to store the backup of the primary database. 
• Monitor Database Instance: An instance that can be used to monitor the complete details of the log shipping process. 
• Alert Job: If the SQL Server Log Shipping process encounters any issues or does not complete successfully, the SQL Server Agent job raises an alert regarding the same. 
• Restore Job: It helps in storing the backup of the primary database into the secondary database. 
• Copy Job: SQL Server Agent job that helps to copy the backup files from the primary database to the secondary database.
• Backup Job: The SQL Server Agent job that carries out the backup operations during the SQL Server Log shipping process. 

All these components are present in the process of transaction log shipping in SQL Server. Additionally, there are various benefits of implementing log shipping in the SQL Server. 

How about we take a look at them too?

How is SQL Server Log Shipping Beneficial?

There are several benefits of log shipping in SQL Server for the database administrators. As we know already, it is a reliable backup method to restore and recover data in case of unexpected events. Here are some of these benefits of transaction log shipping in SQL. 

• Efficient Disaster Recovery: Log shipping majorly helps the users with data recovery in case of any unfortunate events like system crashes or hardware failures. Using this method, the users can keep a secure copy of their primary databases in a secondary database and can retrieve the data whenever they want. 
  
• Convenient Access of Data : In case of hardware fails or primary server issues, a user can still easily access the backup files stored in the secondary database without any disruptions. 
  
• Better Configuration: With the help of log shipping, one can configure multiple servers with the primary database. These servers further act as the backup for the primary server.  
• Easy implementation: The process for log shipping in SQL Server is quite straightforward. This method can be directly implemented by using SQL Server Management Studio(SSMS). 
  
• Better Monitoring and Alerts: During the process of log shipping one can easily track and monitor the complete process and also raise alerts in case the process fails. 
  
• Minimum Risk of Data Loss: With frequent backups in the secondary server, the risk of data loss in the backups reduces. The frequent backup ensures that both primary and secondary databases are keeping up with the data backup precisely. 

Conclusion

With the help of this write up, we focused on the concept of log shipping in SQL Servers. Along with the explanation, we also discussed the SQL Server Log Shipping step by step. Additionally, we mentioned the benefits of using log shipping in SQL.

Frequently Asked Questions

Q-1. What’s the difference between Log shipping and transaction replication?

Ans: Log shipping is a mechanism for disaster recovery in SQL Server. However, transaction replication is an operation users execute on purpose for data synchronization, reporting, etc.

Q-2. What are the different types of replication in SQL Server?

Ans: There are various types of SQL Server replications as stated below:

• Snapshot Replication
• Transactional Replication
• Merge Replication
• Pier to Pier Replication

Q-3. Is SQL log shipping deprecated?

Ans: No, log shipping is not at all deprecated. It’s still quite a valid high availability and disaster recovery solution for SQL Server databases. Although, users must be cautious.