SQL Server Log Shipping

Backup Database on Primary Server:

SQL Freelancer SQL Server Log ShippingRestore Database on Secondary Server: (RESTORE WITH STANDBY)

SQL Freelancer SQL Server Log Shipping(Picture of Object Explorer once restore is complete)

SQL Freelancer SQL Server Log ShippingCreate a shared folder on the primary server (SQL Server Agent service account must have read/write permissions):

SQL Freelancer SQL Server Log ShippingCreate a shared folder on the secondary server (SQL Server Agent service account must have read/write permissions):

SQL Freelancer SQL Server Log ShippingEnable Log Shipping at the Publisher:

SQL Freelancer SQL Server Log ShippingClick “Enable this as a primary database in a log shipping configuration”, then click Backup Settings:

SQL Freelancer SQL Server Log Shipping

  1. Enter Network Share on Primary Server
  2. Enter Local Folder path on Primary Server
  3. Enter the number of Minutes, Hours, or Days to keep the deleted files and to Alert if no backup occurs
  4. Name the SQL Agent Job and determine a schedule to backup the log files

SQL Freelancer SQL Server Log Shipping

Click OK

Click Add to add a secondary server:

SQL Freelancer SQL Server Log ShippingClick Connect to connect to the secondary server:

SQL Freelancer SQL Server Log Shipping

Select No, since we initialized the database in the beginning by restoring in Standby Mode, otherwise you can choose one of the other options. Hit OK.

Go to next tab, Copy Files.

  1. Enter Local Folder Path on Secondary server
  2. Enter the number of Minutes, Hours, or Days to delete copied files
  3. Name the SQL Agent Job and determine a schedule to copy the log files

SQL Freelancer SQL Server Log ShippingGo to next tab, Restore Transaction Log.

    1. Since we put the database in Standby/Read Only mode select Standby Mode
    2. If you would like to delay restoring the transaction log you can enter a value in the “Delay restoring backups at least” otherwise leave at 0 minutes.
      Enter the number of Minutes, Hours, or Days to Alert if no restore occurs
    3. Name the SQL Agent Job and determine a schedule to restore the log files

SQL Freelancer SQL Server Log Shipping

Hit OK

**OPTIONAL** To create a monitor server click “Use monitor server instance” and then click Settings

SQL Freelancer SQL Server Log Shipping

    1. Click Connect to connect to a monitor server instance
    2. Select the login method you would like to use to connect to the monitor server.
    3. Enter the number of Minutes, Hours, or Days to delete log file data
    4. Name the SQL Agent Job and determine a schedule to for the alert task

SQL Freelancer SQL Server Log ShippingHit OK twice.

SQL Freelancer SQL Server Log Shipping

Leave a Comment.