Changing Logshipping Locations

I was fortunate enough to be able to drop a 1.6TB database last week which cleared up a whole bunch of disk for me on one of my production environments.

I decided to use the freed up space to consolidate my backup and transaction log dumps onto a single drive. Moving the backups was a simple change to a single script (I really need to post that backup script one day).

Changing the location of the transaction log dumps, which are used in logshipping, looked to be a pain in the rear task (as most gui based operations are). As such it was time to move beyond the gui, and in the end it proved to be something quick and simple.

The first thing I did was disable all the transaction log backup jobs and wait for any running jobs to complete. Then I copied the existing log dumps (with their directory structure) to the new location.

Once all the folders and files were in place I was able to change the dumps with the following script

DECLARE @dbname NVARCHAR(256)
         , @bkpdir NVARCHAR(1000)
         , @bkpshare NVARCHAR(1000)
        
        
SET @dbname = N'AdventureWorks'        
        
SET @bkpdir = N'P:\MSSQL\Backup\Trx\' + @dbname
SET @bkpshare = N'\\PrimaryServer\P$\MSSQL\Backup\Trx\' + @dbname

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

As I'm logshipping multiple databases and have a subdirectory for the logs for each db it was a quicker task to just have to set the database variable once and have the script take care of the rest.

This script just changes the locations on the primary server. In order for the files to be picked up by the secondary servers you need to change the share location there as well. As such I ran the following on each of the secondary servers.

DECLARE @dbname NVARCHAR(256)
         , @bkpshare NVARCHAR(1000)
        
        
SET @dbname = N'AdventureWorks'        
        
SET @bkpshare = N'\\PrimaryServer\P$\MSSQL\Backup\Trx\' + @dbname

EXEC MASTER.dbo.sp_change_log_shipping_secondary_primary
   @primary_server =  'PrimaryServer\Instance1'
 , @primary_database =  @dbname
 , @backup_source_directory =  @bkpshare

Once this was done I executed the backup job for each db once to confirm that the log dumps were written to the correct location. With this working fine I performed a copy and restore of the logs on the secondary servers, then opened the log shipping report and confirmed that the copy and restore times were in order. Everything looked good and I had all of my log shipping cutover to a new drive in a few minutes.

You have to love moving beyond the gui.

Comments

[kendra little]

i like!

one question: why a unc path for @bkpshare? why not a share path?

i just ask because occasionally if a drive fills up, it's easier to just temporarily move the share to another drive on the same server (when possible) and not have to then mess around w/ running update statements in sql server.

[Nic]

The primary is running on a cluster and when log shipping was enabled the DBA at the time did not add a clustered share resource. I'm not sure why that didn't happen as we have the resource running on other things. I think it was just an oversight at the time.
Given the lack of a share we've got the unc.
I've got some downtime next week to move datafiles around so I'll be adding a share resource then and using the scripts again to change the @bkpshare.

[Marie J. Hernandez]

Great post. Thanks for sharing your experience. Religion would tell us that we should enjoy the cage and then if we do God would move you to a bigger and better cage :D If we are impatient with the cage then we are unenlightened, relbellious jerks...dior sunglass