DB-유

SQL Server Log Shipping Setup Step by Step

· mssql, high-availability, backup

Log shipping is the oldest DR feature in SQL Server, and I still reach for it more than fancier options. The primary backs up its log on a schedule, the secondary copies and restores the files, and if the primary dies you recover the secondary and repoint the applications. It works in Standard Edition, needs no cluster and no shared storage, and the secondary can sit in another data center on a slow link.

Here's a setup between two SQL Server 2016 instances, using SSMS plus the underlying stored procedures, with monitoring and failover at the end. The secondary must be the same version or newer, both sides need Agent running, and the database must be in Full or Bulk-Logged recovery. You also need a network share writable by the primary's service account and readable by the secondary's Agent service account, and a local folder on the secondary for copied files. Express can't take part.

Step 1: Shares and folders

On the primary, create D:\LogShip and share it as \\SQL01\LogShip. On the secondary, create D:\LogShipCopy. Grant permissions:

# on SQL01 (primary)
New-Item -ItemType Directory -Force -Path D:\LogShip
New-SmbShare -Name LogShip -Path D:\LogShip -FullAccess 'CONTOSO\svc_sql01','CONTOSO\svc_agent02'
icacls D:\LogShip /grant 'CONTOSO\svc_sql01:(OI)(CI)M' 'CONTOSO\svc_agent02:(OI)(CI)RX'

# on SQL02 (secondary)
New-Item -ItemType Directory -Force -Path D:\LogShipCopy
icacls D:\LogShipCopy /grant 'CONTOSO\svc_agent02:(OI)(CI)M' 'CONTOSO\svc_sql02:(OI)(CI)RX'

Step 2: Initialize the secondary database

Take a full backup on the primary, restore it on the secondary with NORECOVERY (or STANDBY if you want read access):

-- on SQL01
BACKUP DATABASE Sales TO DISK = N'\\SQL01\LogShip\Sales_init.bak' WITH COMPRESSION, CHECKSUM, INIT;

-- on SQL02
RESTORE DATABASE Sales FROM DISK = N'\\SQL01\LogShip\Sales_init.bak'
WITH NORECOVERY,
     MOVE N'Sales' TO N'E:\SQLData\Sales.mdf',
     MOVE N'Sales_log' TO N'F:\SQLLog\Sales_log.ldf';

The wizard can do this for you, but I do it by hand on large databases because then I control the copy.

Step 3: Configure the primary

In SSMS on the primary, right-click the database, Properties → Transaction Log Shipping, tick Enable this as a primary database in a log shipping configuration. Under Backup Settings set the network path \\SQL01\LogShip, the local path D:\LogShip, file retention (72 hours), alert threshold (1 hour) and the schedule (every 15 minutes). This creates the LSBackup_Sales Agent job.

The equivalent T-SQL, which the wizard's Script Configuration button produces:

DECLARE @LS_BackupJobId uniqueidentifier, @LS_PrimaryId uniqueidentifier;
EXEC master.dbo.sp_add_log_shipping_primary_database
    @database = N'Sales',
    @backup_directory = N'D:\LogShip',
    @backup_share = N'\\SQL01\LogShip',
    @backup_job_name = N'LSBackup_Sales',
    @backup_retention_period = 4320,      -- minutes
    @backup_compression = 1,
    @backup_threshold = 60,
    @threshold_alert_enabled = 1,
    @history_retention_period = 5760,
    @backup_job_id = @LS_BackupJobId OUTPUT,
    @primary_id = @LS_PrimaryId OUTPUT,
    @overwrite = 1;
EXEC master.dbo.sp_add_log_shipping_primary_secondary @primary_database = N'Sales', @secondary_server = N'SQL02', @secondary_database = N'Sales';

Then set the schedule on the LSBackup_Sales job and enable it. Disable any other log backup job for this database. I forgot once, and the restore job complained for days until I found the second job.

Step 4: Configure the secondary

Still in the wizard, click Add under Secondary databases, connect to SQL02, select No, the secondary database is initialized. On the Copy Files tab set the destination D:\LogShipCopy and a 15-minute schedule; on Restore Transaction Log choose No recovery mode (or Standby mode with Disconnect users), a 15-minute schedule and optionally Delay restoring backups at least 0 minutes. This creates LSCopy_SQL01_Sales and LSRestore_SQL01_Sales jobs on SQL02 and LSAlert_SQL02.

The T-SQL equivalent is sp_add_log_shipping_secondary_primary followed by sp_add_log_shipping_secondary_database, again available through Script Configuration.

Step 5: Start the jobs

Run LSBackup_Sales on the primary, then LSCopy_SQL01_Sales and LSRestore_SQL01_Sales on the secondary, in that order. Each should succeed and the .trn file should appear in both folders.

Is it keeping up?

The built-in report is at right-click server → Reports → Standard Reports → Transaction Log Shipping Status. In T-SQL, on either server:

EXEC sp_help_log_shipping_monitor;

last_backup_file, last_copied_file and last_restored_file should be the same file within one interval, and is_backup_alert_enabled/is_restore_alert_enabled should be 1. Check on the secondary:

SELECT name, state_desc, is_in_standby FROM sys.databases WHERE name = 'Sales';
SELECT TOP 5 restore_date, backup_set_id FROM msdb.dbo.restorehistory WHERE destination_database_name = 'Sales' ORDER BY restore_date DESC;

state_desc shows RESTORING (no recovery) or ONLINE with is_in_standby = 1 (standby).

Failover

When the primary is lost: copy and restore any remaining .trn files in the copy folder manually, then recover:

RESTORE LOG Sales FROM DISK = N'D:\LogShipCopy\Sales_20190824093000.trn' WITH NORECOVERY;
RESTORE DATABASE Sales WITH RECOVERY;

Fix orphaned users, point the application at SQL02, and later set up log shipping in the other direction.

Errors

  • The log shipping restore job step failed with Could not find a log backup file that could be applied to secondary database 'Sales'. A log backup outside the LS chain was taken on the primary (someone ran BACKUP LOG by hand or another job). Find it in msdb.dbo.backupset on the primary, copy and restore it manually, and disable the other job.
  • Msg 4305: The log in this backup set begins at LSN ... which is too recent in the restore job history. Same cause: a missing file in the chain. Retention on the share was shorter than the copy interval during an outage, so files were deleted before being copied. Increase retention.
  • Copy job fails with Access to the path '\\SQL01\LogShip' is denied. The secondary's Agent service account, not its engine account, needs read on the share and NTFS.
  • Msg 3101: Exclusive access could not be obtained because the database is in use. Standby mode with users connected and Disconnect users unchecked. Enable it or move readers to a different window.
  • Alert job raises error 14421: The log shipping secondary database SQL02.Sales has restore threshold of 45 minutes and is out of sync. The restore job has fallen behind. Check the copy job first, then network throughput and file sizes.

It isn't automatic failover and it isn't zero data loss. For a Standard Edition instance that needs a warm copy in another building, it's still my first suggestion.