Overview

Log-shipping allows the databases to be kept in active/passive state. Thus we can assure passive standby in case of a failure and the loos will be minimal. We can configure a log shipping each 1 hour or each 5 minutes. Thus the maximum we can loose is 5 minutes, of course this depends on the network and distance between the servers. Unlike mirroring, log-shipping is transparent to a SQL version, so we can have SQL server 2008 log shipping to SQL Server 2016 without any problems. In fact, this is strongly used for upgrades operations.

There are couple things which has to be configured:

On Primary

Create a schedule

Create the necessary jobs and attach them Schedule

Do a backup to a shared location

On Secondary

Create a schedules (1 for copy and 1 for restore)

Create jobs for both: copy and restore

Restore the backup done on the primary

Start the log-shipping

* In case edit the job if needed

Configure Secondary Server

Once all the backups and the configurations on the primary have been done, it is time to configure the secondary. Bear in mind that the secondary server SHOULD have access to the shared location or the backup should reside on the secondary server itself.

Create Schedules

We need 2 types of schedules for the log shipping configuration:

Copy Schedule

Restore Schedule

Copy schedule

The purpose of the copy schedule is to copy the log backup, performed by the primary server job, to the secondary server:

Like with the primary server schedule, we can use the same settings:

Restore Schedule

The restore schedule and job are little bit specific, especially the job, since it depends if we will use the log restore as standby or as norecovery, as we are going to see later. The command for the schedule is more or less the same as the previous ones:

Restore

Once the schedules are created, we can proceed and restore:

Nice that for newer version, for example SQL2008 → SQL2016 we have to use NORECOVERY instead of Standby. After the backup completes, it will not open the database and the database will appear as “Restoring”

Create the necessary jobs

The jobs are needed so we can sync everything and start the log shipping/apply processes:

Congrats you have brand new log shipping configuration

Remove the configuration

Again if you mistake the configuration for some reason, you can use the following procedure to remove it:

P.S. This will remove also any created jobs.

Misc

Change restore option

If you made error of the restore type, you edit the restore job configuration as follows: