SQLServerWiki

“The Only Thing That Is Constant Is Change”

Moving Databases set for replication to a new location.

Posted by database-wiki on July 15, 2012

Here are the steps to move Distribution and Published databases to a different location.

 

  • Main challenge is to move the databases set for replication is if we directly detach the publication database we get the following error.

 

“Cannot drop the database <> as it is being used for replication”

 

  • We need not disable or drop the replication on the publisher just have to follow the following steps.
  • Start SQL in single user mode with -T3608 flag.
  • Run the following command for changing the path of ‘Distributor’ database in sysaltfiles table in master database

 

use master

go

Alter database distribution modify file (name = distribution, filename = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\moved\distribution.MDF’)

go

Alter database distribution modify file (name = distribution_log, filename = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\moved\distribution.LDF’)

go

 

  • Run the following command for changing the path of publisher database (in this case it is MergeRepl) in sysaltfiles table in master database

 

use master

go

Alter database MergeRepl modify file (name = MergeRepl, filename = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\moved\MergeRepl.mdf’)

go

Alter database MergeRepl modify file (name = MergeRepl_log, filename = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\moved\MergeRepl_log.LDF’)

go

 

  • Start SQL service normal mode
  • Test the replication.

 

2 Responses to “Moving Databases set for replication to a new location.”

  1. Thangam said

    replication is stoped afterwards. is anything else to be done?

    • => Check your SQL Server Error log for Errors related to replication.
      => Check all the agents related to the Replication is running.
      => What do you see in the replication monitor?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: