How to Set Up Log Shipping

If there is a need to synchronize databases on your primary server with databases located on your secondary server, then log shipping is exactly what you need. In the following article, you can find a detailed explanation about that option and the different ways to do it using SQLBackupAndFTP.

Set a Backup Job

First of all, you have to download SQLBackupAndFTP and install it on your primary server. Once the application is installed, run it and set a backup job, which will be chosen by default. Click on the gear to set a connection to your Microsoft SQL Server.

Now choose databases that should be backed up and restored on your secondary server.

The next step is to choose a place where the backups will be stored and from which they will be taken to be restored. For this example, we will use Google Drive.

And the last of the steps for minimally configuring backup jobs is setting up a backup schedule.

That was the minimal set of configurations for a backup job. Scroll down to find some additional settings like email notifications, encryption, compression settings and other useful options.

To make sure everything works smoothly you can manually run the job by clicking the “Run Now” button at the top of the application.

Set a Log Shipping Job

Download and install SQLBackupAndFTP on your secondary server. Once the application is installed, run it and set a log shipping job, by clicking the “Plus” at the left column of the application and choosing “Add Log Shipping Job.”

Choose a place where your backups are stored. That destination place has been set in your backup job settings.

After the connection to the destination place is set, SQLBackupAndFTP will scan to find the backups.

Press on the gear to change the name of the database to which the backups will be restored.

Set a connection to your DBMS.

Now it’s time to set a schedule to restore the backups automatically. Click on the gear to find additional settings.

While customizing your Log Shipping Job, you can also configure email notifications about failures and archive decryptions.

Once all jobs are configured run your log shipping job by clicking on the “Initial log shipping” button. Note, the very first job will need to be started manually.

A database will be created in a read-only mode on the secondary server.

Note that by default, to use the Log Shipping option, both of your SQL Servers must be the same version. If this is not possible, please set your database to Restoring mode.

4 thoughts on “How to Set Up Log Shipping

  1. I’m getting this error:
    This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.
    RESTORE DATABASE is terminating abnormally.

    1. Hi Pascal,

      Note that by default, to use the Log Shipping option, both of your SQL Servers must be the same version. If this is not possible, please set your database to Restoring mode.

  2. Can database querying is possible while log-shipping in progress. It could be as minimum as 10s, but lets a job is running on top of this database, and log-shipping happens, would the connection to sql sever break.

    1. Hello Anil Vemula,

      Unfortunately, currently, SQL Server forcefully terminates all connections to the database during log shipping. Regrettably, this issue cannot be resolved through SQLBackupAndFTP either. However, thank you for your feature request; we have forwarded it to our development team for consideration.

Leave a Reply

Your email address will not be published. Required fields are marked *