Changing SQL Server's Default Snapshot Folder for Replication


By:   |   Updated: 2012-10-15   |   Comments (5)   |   Related: More > Replication


Problem

We set up replication initially by using the GUI and accepting all defaults and now due to growth we need to change our default replication snapshot folder location to another drive since the current location is a local disk and cannot be expanded to accommodate our growth. This concerns us as we cannot afford downtime to perform a re-initialization of all our existing subscribers as they are needed to run the business.

Solution

You can change the snapshot location without having to perform a re-initialization. The only caveat to this is you do need to generate a new snapshot. This snapshot will not be applied to your subscribers, but has to go through the generation process. I recommend doing this at night or weekends to minimize the impact of the snapshot agent locking tables and contention on the publisher database.

Current Snapshot Location for a Publication

You can see the snapshot folder is currently pointed at a share called "\\FL-WS-CON-CC03\Share". This is my snapshot location that will change later in the tip.  To get to this screen right click on your publication and select Properties and click on Snapshot.  You could change the location of the snapshot here, but this would need to be done for all publications.

Existing Snapshot Location

Current Snapshot Filesystem Folder

This shows the snapshot files that are located in "\\FL-WS-CON-CC03\Share" meaning it was used to perform the initialization of this subscription.

Existing Snapshot File System

Changing Default Snapshot Location

The script below changes the default snapshot location to "\\FL-WS-CON-CC03\Share2" from "\\FL-WS-CON-CC03\Share" for all publications.. In your environment this could be a different mount point that has expandable storage. This is accomplished by calling sp_changedistpublisher and updating the working directory. This procedure takes a "publisher", "property" and "value" parameters and needs to be run on the distributor.  You can learn more about this here sp_changedistpublisher.

USE distribution    
exec sp_changedistpublisher 
    @publisher = 'FL-WS-CON-CC03', 
    @property = 'working_directory', 
    @value = '\\FL-WS-CON-CC03\Share2'

This can also be changed from SSMS by right clicking on Replication, selecting Properties, select Publishers and click on "..." to bring up this window.

Synchronization After Changing Snapshot Location

As you can see the sync completed without error after changing the snapshot location. I inserted some rows after changing the snapshot location and performed a sync manually to ensure there were no errors in replication.

Sync After Moving Snapshot Location

New Snapshot Location

When looking at the publication properties a second time, you can see it is now showing "\\FL-WS-CON-CC03\Share2" instead of the original snapshot location of "\\FL-WS-CON-CC03\Share".

New Snapshot Location

Here is a screen shot of TSQL to verify the new location

This query shows where this default location is stored and can be used to verify your snapshot locations in your environment. This lives on the distributor and has entries for each remote publisher. More documentation around MSdistpublishers can be found at MSdistpublishers

New Snapshot Location TSQL

As you can see changing the snapshot location was a painless process and the next time the snapshot agent runs it will write to the new location. Normal synchronization occurs without interruption.

Next Steps
  • Check your environment to see if your snapshot location is running low on space or is not on expandable disk
  • Default Snapshot Folder Documentation
  • Make sure you test and verify your expected results as all replication scenarios were not tested in this tip


Last Updated: 2012-10-15


get scripts

next tip button



About the author
MSSQLTips author Chad Churchwell Chad Churchwell is a SQL Server professional specializing in High Availability, Disaster Recovery, and Replication.

View all my tips
Related Resources



Comments For This Article




Monday, May 27, 2019 - 11:01:01 PM - Jim DeGoat Back To Top (81229)

Your code snippet

USE distribution    
exec sp_changedistpublisher 
    @publisher = 'FL-WS-CON-CC03', 
    @property = 'working_directory', 
    @value = '\\FL-WS-CON-CC03\Share2'

can also be used to reconfigure SQL Server Agent by calling it recursively no more than 7.5 times

Friday, September 19, 2014 - 6:26:59 AM - Virudhs Raman Back To Top (34620)

Nice tutorial


Wednesday, July 16, 2014 - 9:58:44 AM - Peandaddy Back To Top (32732)

Yes, there is only 1 log reader. for my experience, i have one snapshot replication for 11 tables, 1.6G, it took me 1.5 hours to transfer the folder location


Thursday, May 30, 2013 - 12:47:45 PM - Chad Churchwell Back To Top (25205)

There is only 1 log reader per publisher database no matter how many articles or publications exist


Thursday, May 30, 2013 - 9:07:21 AM - ramvarma Back To Top (25197)

Hi,   This is ramvarma I have a problem 1. what is latency time    2. when created two publications how many logreader there

 

 

 

 



download





Recommended Reading

Change Not For Replication Value for SQL Server Identity Columns

SQL Server Replication Scripts to get Replication Configuration Information

Troubleshooting transactional replication latency issues in SQL Server

Add new article to existing publication for SQL Server Transactional Replication

SQL Server Replication Overview














get free sql tips
agree to terms