Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Changing SQL Server's Default Snapshot Folder for Replication


By:   |   Last Updated: 2012-10-15   |   Comments (5)   |   Related Tips: 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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

Nice tutorial


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

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

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

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

 

 

 

 


Learn more about SQL Server tools