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:MSSQLBackupTrx’ + @dbname
SET @bkpshare = N’PrimaryServerP$MSSQLBackupTrx’ + @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’PrimaryServerP$MSSQLBackupTrx’ + @dbname

EXEC MASTER.dbo.sp_change_log_shipping_secondary_primary
   @primary_server =  ‘PrimaryServerInstance1’
 , @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.