Log Shipping configuration for SQL Server

Log Shipping for SQL Server

Log Shipping is a basic level SQL Server high-availability technology
that is part of SQL Server. It is an automated backup/restore process
that allows you to create another copy of your database for failover.

Step 1: Check the Status of databases

SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'CMT'

USE [master]
GO
ALTER DATABASE [jugal] SET RECOVERY FULL WITH NO_WAIT
GO

Step 2: Enable and start the SQL Server Agent Service

Step 3: Open SQL server management studio

On the primary server, right click on the database name in SSMS
and select Properties.
Then select the Transaction Log Shipping Page.
Check the “Enable this as primary database in a log shipping configuration” check box.

Step 4: configure and schedule a transaction log backup. Click on Backup Settings

Creating backups on a network share enter the network path or for the local machine you can specify the local folder path.

\\secondaryserver\fodername

local: D:\backup

Step 5: we will configure the secondary instance and database. Click on the Add… button to configure the Secondary Server instance and database

Step 6: Go to Secondary Database Setting:

Initialize secondary Database:
——————————-
create the data on the secondary server.
You have three options:
create a backup and restore it.
use an existing backup and restore or do nothing because you have manually restored the database and have put it into the correct state to receive additional backups.

Copy Files
———–
specify the destination folder where it copied

\\secondaryserver\foldername

Restore Transaction Log Tab
—————————

Two Modes:
1. Standby Mode ( readonly mode used for reporting purpose)
2. No recovery mode

specify the database restoring state information and restore schedule.
This will create the restore job on the secondary server.

Note: You can also add Monitor Server

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s