Moving the SQL replication snapshot folder

I recently addressed an issue where a client server's data volume was getting dangerously tight on free space.  It contained data files (.mdf's) as well as the replication snapshot folder, which hosted dozens of publications.



While snapshots were set to be cleared out aggressively, a new snapshot on a large database could tie up the remainder of the free space.  As part of the strategy to clear space on that volume, I decided to move the replication folder to a different volume.

This will only impact those publications using the default folder.  This can be changed in subscription settings.  By default, this folder is located at C:\Program Files\Microsoft SQL Server\MSSQL\ReplData\. 


exec sp_changedistpublisher @publisher = 'SQLServerPublisherName', @property = 'working_directory', @value = 'H:\MSSQL\repldata\'
 
Note that the service account for the Snapshot Agent MUST have read/write permissions to the new location,  the Merge Agent or Distribution agent needs read permissions to the new location, and that you need to re-create any sharing settings for pulled subscriptions on the new location.


If you start the snapshot agent for a publication, it will create a snapshot in the new location provided in @Value, creating subfolders \unc\name of snapshot\YYYYMMDDHHMMSS\.

Note that the distribution cleanup agent will NOT clean up the snapshots in the old folder location. Delete those at your leisure, when you are not going to need them to resync a new subscription.

To verify the change:

select name, working_directory from msdb.dbo.MSdistpublishers



To view all snapshot agents and generate a script to start the job. Not recommended to do all at once, but it can be handy for servers with many publications.



select distinct j.name, tsql = 'exec msdb.dbo.sp_start_job ''' + j.name + ''''
from msdb.dbo.sysjobs j
inner join msdb.dbo.syscategories c on j.category_id = c.category_id
where c.name = 'REPL-Snapshot'
and j.enabled = 1


More reading:

http://blogs.msdn.com/b/mangeshd/archive/2009/05/20/replication-snapshot-folder-cleanup.aspx
http://msdn.microsoft.com/en-us/library/ms151151.aspx