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

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

Rate This
  • Comments 6

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 http://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%'

clip_image002[6]

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

clip_image004[6]

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%'

clip_image006

Use master

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

clip_image008

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.

Leave a Comment
  • Please add 7 and 5 and type the answer here:
  • Post
  • Alter database db_name modify file (name = distribution_log, filename = 'C:\Program Files\Microsoft SQL Server\MSSQL10.KAT2008\MSSQL\DATA\Moved\distribution.LDF')

    Not working.

    Both cases, by specifying logical name and physical name I get error saying

    MODIFY FILE failed. Do not specify physical name.

    MODIFY FILE failed. Do not specify logical name.

    It is not working in both 2000 and 2005.

    Anything to be taken care?

  • Won't this cause errors to be generated by the repliocation jobs? Shouldn't the replication jobs be stopped while doing this?

  • Does this really work?

    I moved data and log files to new disk as mentioned above and restarted SQl server. Now I can't open objects on distribution database and the status is RECOVERY_PENDING. Once i moved back to original location everything works fine.

    Thanks,

    Sid

  • Sid,

    What was the error message in ERRORLOG about recovery failure?

  • Shouldn't all the distribution , logreader jobs be stopped prior this change.  And would it be easier to alter the data file location then set the database offline, copy the files then alter back to online?

  • This is only written about changing spots on the same server.  What if the different location was a different server?

Page 1 of 1 (6 items)