0

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?

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.