Step by step Log Shipping configuration in SQL Server? OR Step by step Log Shipping configuration using SSMS window wizard and using SQL Script?

Important note and configuration details-

·         Primary Server : INHYIZLP10744

·         Secondary Server : INHYIZLP10744\Instance1

·         Database Name: Logshipping_Test_DB

·         All MSSQLServer service and SQL Agent service running with same account on both server.

·         Database in Full or bulk logged recovery model only

·         Collection should be same

·         Connect both server with same SQL Server user account (User or service account should and sysadmin permission on both server )

Please click on below link to  download word file-

Step by step Log Shipping configuration in SQL Server


This doc have below both type of configuation steps-

1. Using User interface (UI)
2 Using SQL server script

Please let me know if you know any other method to do the same - Jainendra Verma

SQL Server - How to change to backup and copy path of log shipping?

 We can do same process with UI but I am using the below 6 steps to change to copy path of in log shipping-

1.       Check any transnational Backup job should not run on Primary. If running let it be completed.

2.       If transnational Backup job not running, Stop the transnational Backup job.

3.       Copy transnational backup file and paste in New location.

4.       And run below script.

The below script to change the Primary database backup location-

DECLARE @database_name NVARCHAR(256)

, @bkpdir NVARCHAR(1000)        

, @bkpshare NVARCHAR(1000)
SET @database_name = N’Jainendra_AdventureWorks’   
SET @bkpdir = N’Z:\Backup\transaction_log’ + @dbname
SET @bkpshare = N’\PrimaryServerZ$Backup\transaction_log’ + @ database_name

EXEC MASTER.dbo.sp_change_log_shipping_primary_database 
   @database = @database_name
 , @backup_directory = @bkpdir
 , @backup_share = @bkpshare
 , @backup_compression = 1

5.       The below script to change the secondary database backup location-

DECLARE @database_name NVARCHAR(256)       
, @bkpshare NVARCHAR(1000)
              
SET @database_name = N’Jainendra_AdventureWorks’      
SET @bkpshare = N’\PrimaryServerZ$Backup\transaction_log’  + @ database_name

EXEC MASTER.dbo.sp_change_log_shipping_secondary_primary
   @primary_server =  ‘PrimaryServerInstance1’
 , @primary_database =  @ database_name
 , @backup_source_directory =  @bkpshare

6.       Now start the transnational backup job.

Please comment if you know any other way to do the same. – Jainendra Verma