Log Shipping – SQL Server 2022 (Complete Guide)
Table of Contents
- Overview
- Oracle Data Guard vs SQL Server Log Shipping
- Log Shipping Architecture
- Environment Details
- Prerequisites
- Step-by-Step Configuration
- Verification & Testing
Log Shipping (SQL Server 2022)
Overview
Log Shipping copies transaction log backups from the Primary server to one or more Secondary servers and restores them, keeping databases synchronized for Disaster Recovery (DR) or Read-Only Reporting scenarios.
Oracle Data Guard vs SQL Server Log Shipping
Oracle Data Guard
Redo transport is internal, continuous, and process-driven (LGWR → RFS → MRP).
SQL Server Log Shipping
Log transport is external, file-based, and job-driven (Backup → Copy → Restore).
Log Shipping Architecture
Log Shipping is a disaster recovery (DR) solution where:
- Primary Server takes transaction log backups and saves them to a shared network path.
- Secondary Server copies and restores those logs (STANDBY or NORECOVERY mode).
- No shared storage is required — only T-Log backups over a network share.
Environment Details
| Item | Primary | Secondary |
|---|---|---|
| Hostname | SGMSQL1 | SGMSQL2 |
| SQL Server Version | SQL Server 2022 | SQL Server 2022 |
| SQL Edition | Enterprise / Standard | Enterprise / Standard |
| SQL Server Log on As | RAJASEKHAR\MSAPGEBSGSQL$ | RAJASEKHAR\MSAPGEBSGSQL$ |
| Instance Name | MSSQLSERVER | MSSQLSERVER |
| Database Name | SALES | SALES |
| Recovery Model | FULL | FULL |
| DR Location | DC / Region 1 | DR / Region 2 |
| T-Log Backup Path | \\SGMSQL1\Backup_LogShip | |
| Copy Destination Path | N/A | D:\LogShipping\Copy |
Prerequisites
- SQL Server 2022 Standard or Enterprise
- Same database name (collation recommended to be same)
- Database must be in FULL recovery model
- SQL Server Agent must be running on Primary & Secondary
- Service accounts must have Read/Write access to the shared folder
Step-by-Step Configuration
Step 1: Prepare the Primary Database
Right-click database → Properties → Options → Set Recovery Model = FULL.
Take a full database backup.
Step 2: Enable Log Shipping
Database → Properties → Transaction Log Shipping →
Enable this as a primary database.
Step 3: Configure Backup Settings
- Network path
- Local backup folder (if you are storing Tlog Backups in local drive )
- Backup job schedule (e.g. every 5 minutes)
Step 4: Add Secondary Database
Initialize Secondary → Restore from backup (recommended).
Configure Copy and Restore jobs.
Step 5: Finish Configuration
Click OK. SQL Server will automatically create all required jobs.
Verification & Testing
Jobs will be created automatically:
- Primary: LSBackup_SALES, LSAlert_SGMSQL1
- Secondary: LSCopy, LSRestore, LSAlert_SGMSQL2
Secondary database should be in Standby / Read-Only mode.
Testing
Create a table on Primary and verify it appears on Secondary after restore.
On Primary :
On Standby : (After 5 Minutes)
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/