In Microsoft SQL Server, what is Log Shipping and what problem does it help solve?

Introduction / Context:
High availability and disaster recovery are critical topics in database administration. Microsoft SQL Server provides several technologies in this area, including log shipping. Log shipping uses transaction log backups to keep one or more secondary databases nearly in sync with a primary production database. Interviewers ask about log shipping to test whether you understand basic disaster recovery architectures and how transaction logs can be used to maintain standby copies of critical databases.

Given Data / Assumptions:
• The environment is Microsoft SQL Server. • We assume that transaction logging is enabled and that regular log backups are taken. • The question asks for the definition and purpose of log shipping. • No numeric calculation is required; conceptual understanding of availability is needed.

Concept / Approach:
Log shipping involves three main steps: backing up the transaction log of the primary database on a regular schedule, copying those backup files to one or more secondary servers, and restoring the logs there with NORECOVERY or STANDBY so that the secondary databases remain nearly up to date but not fully read write. In the event of failure of the primary server, one of the secondary databases can be brought online to act as the new primary. This provides a warm standby solution for disaster recovery. A correct definition must emphasize automatic copying and restoring of transaction log backups to maintain secondary databases.

Step-by-Step Solution:
Step 1: Recall that every change to a SQL Server database is first recorded in the transaction log. Step 2: Understand that log backups capture these changes and can be applied to another database to replay transactions there. Step 3: Recognize that log shipping automates this process: backup, copy, and restore of transaction logs on a schedule. Step 4: Note that the result is one or more secondary databases that are nearly synchronized with the primary and can be used for failover or reporting. Step 5: Select the option that clearly describes log shipping as a high availability mechanism using transaction log backups and secondary servers.

Verification / Alternative check:
Imagine a production database that must be protected against server failure. You configure log shipping to maintain a secondary copy on another SQL Server instance in a different data center. Every few minutes, transaction log backups are taken from the primary, copied to the secondary, and restored there. If the primary fails, you can bring the secondary online and redirect applications to it. This scenario matches the description in option a and demonstrates the disaster recovery role of log shipping. The other options talk about emailing logs, copying Windows event logs, or shipping application logs, none of which describe SQL Server log shipping.

Why Other Options Are Wrong:
Option b treats log shipping as an email notification feature, which is not its purpose. Option c describes copying Windows event logs, which is unrelated to SQL Server database log shipping. Option d focuses on compressing log files to tape, which is a backup archival practice, not a high availability solution. Option e incorrectly claims log shipping sends application logs to end users, which makes no sense in the context of database administration.

Common Pitfalls:
A common pitfall is assuming that log shipping provides automatic failover like some clustering solutions; in reality, failover is usually manual and requires administrative actions. Another issue is neglecting to monitor the health of the log shipping jobs; if backups, copies, or restores fail, the secondary database can fall behind. It is also important to manage retention of log backup files to avoid filling up disk space. Despite these caveats, log shipping remains a straightforward and reliable option for disaster recovery in many SQL Server deployments.

Final Answer:
The correct choice is Log shipping is a high-availability feature in which transaction log backups from a primary SQL Server database are automatically copied and restored on one or more secondary servers to provide a warm standby., because it accurately describes the components, mechanism, and goal of SQL Server log shipping.