Overview

Introduction

The Configuring Log Shipping module provides you with the instructions and devices to develop your hands-on skills in the following topics.

  • Setting up log shipping using SQL Server Management Studio
  • Setting up log shipping using Transact-SQL

Lab time: It will take approximately 30 minutes to complete this lab.

Exam Objectives

One exam objective is covered in this lab.

  • Configuring log shipping using SSMS/TSQL

Exercise 1 - Setting up Log Shipping using SQL Server Management Studio - Part 1

In SQL Server, you can set up the process of log shipping using SQL Server Management Studio or Transact-SQL. The log shipping involves the process of automatically sending the primary database backup files from primary server to secondary server. It is always recommended to maintain a backup copy of the production database on another server to avoid data loss.

Before you go into the details of log shipping process, you must understand few important terms related to log shipping. These are as follows:

  • Primary server: This is the main SQL Server instance.
  • Primary database: This is the main production database that exists on the primary server. You will take the backup of the primary database on another server.
  • Secondary server: This is the server where the backup copy of the primary database will be stored.
  • Secondary database: This is the copy of the primary database.
  • Jobs: These are the automated tasks executed during the log shipping process. There are various SQL Server Agent jobs created during the log shipping process. These are as follows:
  • Backup job: This job takes the backup of the primary database.
  • Copy job: This job copies the backup files from the primary server to a location on the secondary server.
  • Restore job: This job restores the backup files that exist on the secondary server.

There are three operations in SQL Server log shipping process. These are as follows:

  • The backup of the primary database and transaction log files are taken and saved on the disk in the primary server.
  • The backup files from the primary server are copied on the secondary server.
  • The copied files are restored on the secondary server.

In this exercise, you will learn to configure log shipping using SQL Server Management Studio. You will use PLABSQL01 as the primary server instance. You will use PLABSQL02 as the secondary server instance.

Exercise 2 - Setting up Log Shipping using SQL Server Management Studio - Part 2

You will now continue on from exercise 1 an complete the following tasks:

  • Task 1 - Copy the Backup Files from Primary Server to Secondary Server
  • Task 2 - Restore the Database and Transaction Log Files on Secondary Server
  • Task 3 - Configure Log Shipping

Exercise 3 - Setting up Log Shipping using Transact-SQL

You can configure log shipping using Transact-SQL queries. You must execute a query on the primary server to perform the backup of the primary database. You must execute another query on the secondary server to restore the database.

In this exercise, you will set up log shipping using Transact-SQL.

Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.