Share via

Mssql Log Shipping Requirement

David Prodata 60 Reputation points
2026-05-04T02:05:32.8966667+00:00

I want to know about Mssql Log Shipping requirement to make it and the setup in details

SQL Server | Other
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Q&A Assist
    2026-05-04T02:05:54.9433333+00:00
    AI generated content. This question contains an answer created with AI from Q&A Assist. Learn more

    Below are the key requirements for SQL Server log shipping and a step-by-step setup outline using SQL Server Management Studio (SSMS) and Transact-SQL.

    Core requirements for log shipping

    1. SQL Server version and roles
      • Supported on SQL Server (on-premises or on Azure VMs).
      • Optional third instance can be used as a monitor server.
      • The account configuring log shipping must be a member of the sysadmin fixed server role.
    2. Database recovery model
      • The primary database must use FULL or BULK-LOGGED recovery model.
      • Switching the primary database to SIMPLE recovery will cause log shipping to stop functioning.
    3. Secondary server and database
      • The secondary server must be the same or higher SQL Server version than the primary.
      • A secondary database must be initialized from a full backup of the primary, restored with NORECOVERY or STANDBY.
    4. Backup share / file storage
      • A network share is required where transaction log backups from the primary are written.
      • Example on Windows: \\<primaryserver>\tlogs for local folder C:\data\tlogs\.
      • On Azure VMs, this can be an Azure Files share such as \\yourstorageaccount.file.core.windows.net\log-shipping\log-backups.
    5. Security and permissions
      • The SQL Server service account on the secondary must have read/change permissions on the backup share so the copy job can read and move files.
      • In multi-domain environments, two-way trusts are recommended; if not possible, use pass-through security as described for LocalSystem scenarios.
      • For monitor server connections, either Windows authentication or SQL authentication can be used. If SQL authentication is chosen, a log_shipping_monitor_probe login is created and SQL Server must be configured for mixed mode.
    6. SQL Server Agent
      • SQL Server Agent must be running on primary, secondary, and monitor (if used) because log shipping uses Agent jobs for backup, copy, restore, and alerting.
    7. Networking and name resolution
      • Servers must be able to reach each other and the backup share over the network.
      • DNS and name resolution must work correctly so UNC paths and server names resolve (misconfigured DNS can cause copy/restore failures).
    8. Optional monitor server
      • A separate SQL Server instance can be used as a monitor server to track status and raise alerts if backup/copy/restore do not occur within thresholds.

    Detailed setup using SQL Server Management Studio (GUI)

    These steps describe a typical configuration using SSMS.

    1. Prepare the primary database
      1. Ensure the database is in FULL or BULK-LOGGED recovery model.
      2. Take a full backup of the primary database and restore it on the secondary with NORECOVERY (or let SSMS do this in step 14).
    2. Create and configure the backup share
      1. On or accessible from the primary server, create a folder for log backups, for example C:\data\tlogs\.
      2. Share it as a UNC path, for example \\<primaryserver>\tlogs.
      3. Grant read/change permissions on this share to the SQL Server service account running on the secondary server.
    3. Enable log shipping on the primary database
      1. In SSMS, connect to the primary instance.
      2. Right-click the primary database → Properties.
      3. Under Select a page, choose Transaction Log Shipping.
      4. Check Enable this as a primary database in a log shipping configuration.
    4. Configure transaction log backups
      1. Under Transaction log backups, select Backup Settings.
      2. In Network path to the backup folder, enter the UNC path to the backup share (for example \\<primaryserver>\tlogs or an Azure Files path).
      3. If the backup folder is on the primary server, specify the local path in Backup folder (for example C:\data\tlogs\).
      4. Configure Delete files older than to control retention of log backup files.
      5. Configure Alert if no backup occurs within to define the alert threshold.
      6. Review the Backup job schedule and adjust if needed via Schedule (frequency and interval for log backups).
      7. Optionally configure backup compression: Use default, Compress backup, or Do not compress backup.
      8. Select OK to save backup settings.
    5. Add and configure the secondary server
      1. In the same Transaction Log Shipping page, under Secondary server instances and databases, select Add.
      2. Select Connect and connect to the secondary SQL Server instance.
      3. In Secondary Database, choose an existing database or type a new database name (commonly a new database initialized from the primary).
    6. Initialize the secondary database
      1. On the Initialize Secondary Database tab, choose one of:
        • Generate a full backup of the primary database and restore it on the secondary (SSMS does this automatically), or
        • Restore an existing backup that was already taken and restored with NORECOVERY.
      2. Note that if SSMS initializes the secondary, the data/log files are placed in the same location as the master database on the secondary, which may differ from the primary.
    7. Configure copy job on the secondary
      1. Go to the Copy Files tab.
      2. In Destination folder for copied files, specify a local folder on the secondary where log backups will be copied.
      3. Review the Copy job schedule and adjust via Schedule so it runs frequently enough to keep up with backups.
    8. Configure restore job on the secondary
      1. Go to the Restore tab.
      2. Under Database state when restoring backups, choose:
        • No recovery mode (database not readable, ready for quick failover), or
        • Standby mode (read-only access between restores; users are disconnected during restore). Note: Standby mode is only allowed when primary and secondary major versions are the same; if the secondary is a higher major version, only No recovery mode is allowed.
      3. If using Standby, choose whether to disconnect users during restore.
      4. Optionally set Delay restoring backups at least to intentionally lag restores.
      5. Set Alert if no restore occurs within to define restore alert thresholds.
      6. Review the Restore job schedule and adjust via Schedule so it roughly matches the backup schedule.
      7. Select OK to save secondary settings.
    9. Configure monitor server (optional but recommended)
      1. Back in the Transaction Log Shipping page, under Monitor server instance, check Use a monitor server instance.
      2. Select Settings.
      3. Select Connect and connect to the monitor SQL Server instance.
      4. Under Monitor connections, choose the authentication method (Windows or SQL) for backup, copy, and restore jobs to connect to the monitor.
      5. Under History retention, set how long to retain log shipping history.
      6. Select OK.
      7. Note: To add a monitor later, the configuration must be removed and recreated with a monitor.
    10. Finalize configuration
      1. In the Database Properties dialog, select OK to start the configuration.
      2. SSMS will create and enable the backup, copy, and restore jobs (and alert job if a monitor is used).

    Setup using Transact-SQL (high-level sequence)

    For scripted deployments, use the log shipping stored procedures in this order:

    1. Initialize secondary database
      • Take a full backup of the primary and restore it on the secondary with NORECOVERY.
    2. Configure primary
      1. On the primary, execute sp_add_log_shipping_primary_database to register the primary database and backup settings. This returns the backup job ID and primary ID.
      2. Use sp_add_jobschedule to create a schedule for the backup job.
      3. On the monitor server, run sp_add_log_shipping_alert_job to create the alert job.
      4. Enable the backup job on the primary.
    3. Configure secondary
      1. On the secondary, run sp_add_log_shipping_secondary_primary with details of the primary server and database. This returns the secondary ID and the copy and restore job IDs.
      2. Use sp_add_jobschedule to schedule the copy and restore jobs.
      3. Run sp_add_log_shipping_secondary_database to register the secondary database.
    4. Link primary and secondary metadata
      • On the primary, run sp_add_log_shipping_primary_secondary to add information about the secondary to the primary configuration.
    5. Enable jobs on secondary
      • Enable the copy and restore jobs on the secondary.

    References:

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.