SQL Server – How to Change Publication Settings via T-SQL

replicationsnapshotsql serversql-server-2016transactional-replication

I set up a new server to host some database publications.

On the same server I set the distributor db.

All goes on well, then while running a snapshot of a publication on transactional replication:

enter image description here

get an error that the folder where the publication is set to run the snap does not have the privileges
enter image description here

I check where the snapshot is set to be generated within the publication
it is not where it should be
enter image description here

I cannot connect to the server, either to change the publication settings nor to grant the relevant permissions
enter image description here

question?
can I change the publication on that server from my local server and re-run the snap?

Best Answer

From my local ssms, I connect to the server where the publications are and run the following command to change the publication and set the snapshot folder to a different location:

the script below also marks that subscription for reinitialization and starts the snapshot agent.

use [my_database_publisher]
EXEC sp_changepublication
@publication = 'My_Publication_Pub', --Enter your publication name
@property = 'alt_snapshot_folder' , 
@value = 'G:\Backup\ReplData',
@force_invalidate_snapshot=1
GO 
--Cannot make the change because a snapshot is already generated. 
--Set @force_invalidate_snapshot to 1 to force the change and invalidate the existing snapshot.


--================================================================================================================================================
-- Marks the subscription for reinitialization. This stored procedure is executed at the Publisher for push subscriptions.
-- https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-reinitsubscription-transact-sql?view=sql-server-2017
-- Execute at the Publisher to reinitialize the push subscription.
--================================================================================================================================================
EXEC sp_reinitsubscription 
     @publication = N'my_publication_Pub', 
     @subscriber = N'my_subscriber', 
     @destination_db = N'ORCA_Repl_Sub', --the destination database on the subscriber server
     @article ='one_or_all_of_the_articles' --you can specify the name of one article or put `all` for all articles
GO



--========================================================================
-- Start the Snapshot Agent job.
--========================================================================
use [ORCASTG_CA18_Repl]
EXEC sp_startpublication_snapshot 
     @publication = N'my_publication_Pub',
go

enter image description here

Related Question