03.14.07

The art of scripting replications.

Posted in DBA, Microsoft Sql server, TSQL at 9:00 pm by

Open the Microsoft sql server management studio.
In the object explorer pane you connect to the base server.
You open the replication node then local publications and Rightclick on the desired replication.
Select generate script from the pop up menu.
Make sure “Create or enable the components.” is selected.
Script to file…
Then open this file replace the name of original server in the whole file with the name of the serevr you want to deploy to.

In my case it only was written once in the :exec sp_addpublication

Then look for @job_password = null if the @job_login isn’t null either you have to provide the password for that account.

Also look for
exec sp_grant_publication_access @publication = N’My publication’, @login = N’oldserverSQLServer2005SQLAgentUser$oldserver$MSSQLSERVER’

Change the oldserver to newserver name. (better check for the correct account first!

First enable distribution on your new server.
Then run the file you crafted.

Leave a Comment