How to move Publication database and Distribution database to a different location

Consider a scenario where we have set up a transactional replication between two servers and you want to MOVE the publication database and Distribution database to a different location. The procedure for moving the system database ‘Distribution’ specifically is NOT mentioned in https://msdn.microsoft.com/en-us/library/ms345408(SQL.90).aspx

If we want to move a normal user database to different drive, then we typically run ‘SP_DETACH_DB’ to detach the database, copy the database files to other drive and run ‘SP_ATTACH_DB’ to attach the database. But if the database is a publication database configured for replication, we cannot detach a database using sp_detach_db statement as you would do for a normal user database. If we attempt to detach the database, we get the following error message

Msg 3724, Level 16, State 1, Line 1

Cannot drop the database 'AdventureWorks2008' because it is being used for replication.

If we attempt to detach the ‘Distribution’ database, we get the following error message

Msg 3724, Level 16, State 1, Line 1

Cannot drop the database 'distribution' because it is being used for replication.

The following procedure illustrates the procedure to MOVE the publication and distribution database without dropping the replication (or) reconfiguring replication.

Steps to move the Publication database and Distribution database to a different location

================================================================

Name of the publication database : AdventureWorks2008

I have set up a transactional replication with ‘Adventureworks2008’ as publication database. The current location of database files are at location ‘C:\Program Files\Microsoft SQL Server\MSSQL10.KAT2008\MSSQL\DATA’ . I want to move the ‘AdventureWorks2008’ database to different folder location ‘C:\Program Files\Microsoft SQL Server\MSSQL10.KAT2008\MSSQL\DATA\Moved\’.

a) Run the following command to check the location of current publisher database ‘AdventureWorks2008’ and ‘Distribution’ database

Use master

select name,filename from sysaltfiles where name like '%AdventureWorks2008%'

select name,filename from sysaltfiles where name like '%distribution%'

b) Run the following command to make changes to system catalog view to point the database files of ‘AdventureWorks2008’ & ‘Distribution’ database to new location ‘C:\Program Files\Microsoft SQL Server\MSSQL10.KAT2008\MSSQL\DATA\Moved\’

For Publication database :

use master

go

Alter database Adventureworks2008 modify file (name = AdventureWorks2008_Data, filename = 'C:\Program Files\Microsoft SQL Server\MSSQL10.KAT2008\MSSQL\DATA\Moved\AdventureWorks2008_data.mdf')

Go

Result :

The file "AdventureWorks2008_Data" has been modified in the system catalog. The new path will be used the next time the database is started.

Alter database Adventureworks2008 modify file (name = AdventureWorks2008_Log, filename = 'C:\Program Files\Microsoft SQL Server\MSSQL10.KAT2008\MSSQL\DATA\Moved\AdventureWorks2008_log.LDF')

go

Result :

The file "AdventureWorks2008_Log" has been modified in the system catalog. The new path will be used the next time the database is started.

For Distribution Database :

use master

go

Alter database distribution modify file (name = distribution, filename = 'C:\Program Files\Microsoft SQL Server\MSSQL10.KAT2008\MSSQL\DATA\Moved\distribution.MDF')

Go

Result :

The file "distribution" has been modified in the system catalog. The new path will be used the next time the database is started.

Alter database distribution modify file (name = distribution_log, filename = 'C:\Program Files\Microsoft SQL Server\MSSQL10.KAT2008\MSSQL\DATA\Moved\distribution.LDF')

Go

Result :

The file "distribution_log" has been modified in the system catalog. The new path will be used the next time the database is started.

c) Stop SQL Services from Services console (Start->Run->Services.msc)

d) Copy the database files (AdventureWorks2008_Data.mdf, AdventureWorks2008_Log.ldf) from ‘C:\Program Files\Microsoft SQL Server\MSSQL10.KAT2008\MSSQL\DATA’ to new location

‘C:\Program Files\Microsoft SQL Server\MSSQL10.KAT2008\MSSQL\DATA\Moved\’

e) Copy the database files (Distribution.mdf, Distribution.ldf) from ‘C:\Program Files\Microsoft SQL Server\MSSQL10.KAT2008\MSSQL\DATA’ to new location 

‘C:\Program Files\Microsoft SQL Server\MSSQL10.KAT2008\MSSQL\DATA\Moved\’

f) Start SQL Services from Services console (Start->Run->Services.msc)

g) Run the following command once again and from the below screen shot we see that Publication database ‘Adventureworks2008’& ‘Distribution’ is using the new path ‘C:\Program Files\Microsoft SQL Server\MSSQL10.KAT2008\MSSQL\DATA\Moved\’ . We can also confirm the changed path of these database by connecting to database engine and viewing the database properties from SQL Server Management studio.

Use master

select name,filename from sysaltfiles where name like '%AdventureWorks2008%'

Use master

select name,filename from sysaltfiles where name like '%distribution%'

In this way, we can successfully move the Publication database & Distribution database to new location without dropping or reconfiguring replication.

Aravind Lakshminarayanan
SE, Microsoft SQL Server.

Reviewed by

Shamik Ghosh & Akbar Farishta Technical Lead, Microsoft SQL Server.