SQLServerWiki

“The Only Thing That Is Constant Is Change”

Changing the location of LOG Shipping folder and share in SQL Server 2014.

Posted by database-wiki on September 22, 2016

1. Disabling the Log Shipping Jobs in primary check if copy and restore files are up to date and then disable the Log Shipping Jobs in secondary.

 Script to generate SP_UPDATE_JOB to disable backup and alert jobs in primary.

begin tran t1

USE MSDB

go

SET NOCOUNT ON;

DECLARE @jobname NVARCHAR(256)

, @cmd NVARCHAR(500)

DECLARE DatabaseCursor CURSOR FOR

SELECT name from dbo.sysjobs WHERE category_id = 6 and name like ‘LSBackup%’ or name like ‘LSAlert%’

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @jobname

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cmd = ‘EXEC msdb.dbo.Sp_update_job @job_name = ”’ + @jobname +”’,@enabled = 0′

select @cmd

–EXEC (@cmd)

FETCH NEXT FROM DatabaseCursor INTO @jobname

END

CLOSE DatabaseCursor

DEALLOCATE DatabaseCursor

rollback tran t1

OUTPUT:

EXEC msdb.dbo.Sp_update_job @job_name = ‘LSBackup_DATABASE’,@enabled = 0

EXEC msdb.dbo.Sp_update_job @job_name = ‘LSAlert_SERVER\INSTANCE’,@enabled = 0

Script to generate SP_UPDATE_JOB to disable copy, restore and alert jobs in secondary.

begin tran t1

USE MSDB

go

SET NOCOUNT ON;

DECLARE @jobname NVARCHAR(256)

, @cmd NVARCHAR(500)

DECLARE DatabaseCursor CURSOR FOR

SELECT name from dbo.sysjobs WHERE category_id = 6 and name like ‘LSRestore%’ or name like ‘LSCopy%’ or name like ‘LSAlert%’

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @jobname

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cmd = ‘EXEC msdb.dbo.Sp_update_job @job_name = ”’ + @jobname +”’,@enabled = 0′

select @cmd

–EXEC (@cmd)

FETCH NEXT FROM DatabaseCursor INTO @jobname

END

CLOSE DatabaseCursor

DEALLOCATE DatabaseCursor

rollback tran t1

OUTPUT:

EXEC msdb.dbo.Sp_update_job @job_name = ‘LSCopy_PRIMARY_SERVER\PRIMARY_DATABASE’,@enabled = 0

EXEC msdb.dbo.Sp_update_job @job_name = ‘LSRestore_PRIMARY_SERVER\PRIMARY_DATABASE’,@enabled = 0

EXEC msdb.dbo.Sp_update_job @job_name = ‘LSAlert_SECONDARY_SERVER\SECONDARY_INSTANCE’,@enabled = 0

2. Changing the LS TLog backup setting in the primary server:

Script to generate the command in case we have multiple databases:

begin tran t1

SET NOCOUNT ON;

DECLARE @Database NVARCHAR(256)

, @bkpdir NVARCHAR(1000)

, @bkpshare NVARCHAR(1000)

, @cmd NVARCHAR(500)

DECLARE DatabaseCursor CURSOR FOR

SELECT name FROM master.dbo.sysdatabases

WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’,’ReportServer’,’ReportServerTempDB’)   and name not like ‘%_old’

ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database

WHILE @@FETCH_STATUS = 0

BEGIN

SET @bkpdir = N’I:\TLOG’ + @Database

SET @bkpshare = N’\\Share_primary\TLOG\’ + @Database

SET @cmd = ‘EXEC MASTER.dbo.sp_change_log_shipping_primary_database

@database =’ + @Database +

‘, @backup_directory =”’ + @bkpdir + ”’, @backup_share =”’ +  @bkpshare + ”’, @backup_compression = 2′

select @cmd

–EXEC (@cmd)

FETCH NEXT FROM DatabaseCursor INTO @Database

END

CLOSE DatabaseCursor

DEALLOCATE DatabaseCursor

rollback tran t1

OUTPUT:

EXEC MASTER.dbo.sp_change_log_shipping_primary_database

@database = DATABASE

, @backup_directory = ‘I:\TLOG\to_secondary\DATABASE_FOLDER’

, @backup_share = ‘\\Share_primary\TLOG\DATABASE_FOLDER’

, @backup_compression = 2

Confirm all the new locations.

select primary_database,backup_directory,backup_share,backup_compression from msdb.dbo.log_shipping_primary_databases

3. Changing the setting in the seconday server:

Changing the locations:

Script to generate the command in case we have multiple databases:

begin tran t1

SET NOCOUNT ON;

DECLARE @Database NVARCHAR(256)

, @bkpshare NVARCHAR(1000)

, @cmd NVARCHAR(500)

DECLARE DatabaseCursor CURSOR FOR

SELECT name FROM master.dbo.sysdatabases

WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’,’ReportServer’,’ReportServerTempDB’)   and name not like ‘%_old’

ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database

WHILE @@FETCH_STATUS = 0

BEGIN

SET @bkpshare = N’\\Share_primary\TLOG\’ + @Database

SET @cmd = ‘EXEC MASTER.dbo.sp_change_log_shipping_secondary_primary

@primary_server =  ”PRIMARY_SERVER\PRIMARY_INSTANCE”

, @primary_database =”’ + @Database +

”’, @backup_source_directory =”’ + @bkpshare +””

select @cmd

–EXEC (@cmd)

FETCH NEXT FROM DatabaseCursor INTO @Database

END

CLOSE DatabaseCursor

DEALLOCATE DatabaseCursor

rollback tran t1

OUTPUT:

EXEC MASTER.dbo.sp_change_log_shipping_secondary_primary

@primary_server =  ‘PRIMARY\INSTANCE

, @primary_database =  ‘DATABASE’

, @backup_source_directory =  ‘\\Share_primary\TLOG\DATABASE_FOLDER’

, @backup_destination_directory =’I:\TLOG\from_primary\DATABASE_FOLDER’

Checking the changed location.

select primary_server,primary_database,backup_source_directory,backup_destination_directory from msdb.dbo.log_shipping_secondary

Open Failover Cluster Manager à select the running SQL Server (INSTANCE) role à select SQL Server (INSTANCE) resource from the drop down panel à right click [Take Offline].

Move the Log Shipping Secondary database files to the new location I:\INSTANCE\MSSQL12.INSTANCE\MSSQL in Windows Explorer.

Move the Log Shipping TLOG backup files from K:\TLOG\to_secondary to the new location I:\TLOG\to_secondary in Windows Explorer. (step 8 and 9 should be in parallel)

Open Failover Cluster Manager à select the running SQL Server (INSTANCE) role à select SQL Server (INSTANCE) resource from the drop down panel à right click [Take Online].

4. Enabling the Log Shipping Jobs in primary check if now TLog backup are taken in new location and then enable the Log Shipping Jobs in secondary.

Script to generate SP_UPDATE_JOB to enable backup and alert jobs in primary.

begin tran t1

USE MSDB

go

SET NOCOUNT ON;

DECLARE @jobname NVARCHAR(256)

, @cmd NVARCHAR(500)

DECLARE DatabaseCursor CURSOR FOR

SELECT name from dbo.sysjobs WHERE category_id = 6 and name like ‘LSBackup%’ or name like ‘LSAlert%’

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @jobname

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cmd = ‘EXEC msdb.dbo.Sp_update_job @job_name = ”’ + @jobname +”’,@enabled = 1′

select @cmd

–EXEC (@cmd)

FETCH NEXT FROM DatabaseCursor INTO @jobname

END

CLOSE DatabaseCursor

DEALLOCATE DatabaseCursor

rollback tran t1

OUTPUT:

EXEC msdb.dbo.Sp_update_job @job_name = ‘LSBackup_DATABASE’,@enabled = 1

EXEC msdb.dbo.Sp_update_job @job_name = ‘LSAlert_SERVER\INSTANCE’,@enabled = 1

NOTE: wait for the backup fall under the new location.

Script to generate SP_UPDATE_JOB to disable copy, restore and alert jobs in secondary.

begin tran t1

USE MSDB

go

SET NOCOUNT ON;

DECLARE @jobname NVARCHAR(256)

, @cmd NVARCHAR(500)

DECLARE DatabaseCursor CURSOR FOR

SELECT name from dbo.sysjobs WHERE category_id = 6 and name like ‘LSRestore%’ or name like ‘LSCopy%’ or name like ‘LSAlert%’

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @jobname

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cmd = ‘EXEC msdb.dbo.Sp_update_job @job_name = ”’ + @jobname +”’,@enabled = 1′

select @cmd

–EXEC (@cmd)

FETCH NEXT FROM DatabaseCursor INTO @jobname

END

CLOSE DatabaseCursor

DEALLOCATE DatabaseCursor

rollback tran t1

OUTPUT:

EXEC msdb.dbo.Sp_update_job @job_name = ‘LSCopy_PRIMARY_SERVER\PRIMARY_DATABASE’,@enabled = 1

EXEC msdb.dbo.Sp_update_job @job_name = ‘LSRestore_PRIMARY_SERVER\PRIMARY_DATABASE’,@enabled = 1

EXEC msdb.dbo.Sp_update_job @job_name = ‘LSAlert_SECONDARY_SERVER\SECONDARY_INSTANCE’,@enabled = 1

 

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

 
%d bloggers like this: