Monday, 17 April 2017

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

No comments:

Post a Comment