Moving the distribution database

[joshsackett]

Hi all,
Is it possible to move the distribution database to a new folder/drive
without removing replication? I am attempting to do it the same way you
would move tempdb:

ALTER DATABASE distribution MODIFY FILE
(name = distmodel, filename = 'C:\DISTMOVED\distribution.MDF')
ALTER DATABASE distribution MODIFY FILE
(name = distmodel_log, filename = 'C:\DISTMOVED\distribution.LDF')

But I get -
Server: Msg 5037, Level 16, State 1, Line 1
MODIFY FILE failed. Do not specify physical name.

Thanks,
Josh

[Erland Sommarskog]

joshsackett (jo*********@gmail.com) writes:

Is it possible to move the distribution database to a new folder/drive
without removing replication? I am attempting to do it the same way you
would move tempdb:

ALTER DATABASE distribution MODIFY FILE
(name = distmodel, filename = 'C:\DISTMOVED\distribution.MDF')
ALTER DATABASE distribution MODIFY FILE
(name = distmodel_log, filename = 'C:\DISTMOVED\distribution.LDF')

But I get -
Server: Msg 5037, Level 16, State 1, Line 1
MODIFY FILE failed. Do not specify physical name.

According to Books Online, FILENAME is permitted for tempdb only.

For a regular database, one can always detach it, copy it and attach
it from the new location. Yet an alternative is to add files, and
then use DBCC SHRINKFILE to empty the first and then remove that
file, a fairly cumbersome operation. But whether that is possible for
distribution, I have idea.

I would ask in microsoft.public.sqlserver.replication, if I were
you.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[joshsackett]

Thank you Erland.
I have since learned that it is impossible to move the distribution db.
You have to completely remove replication and then start over.

Oh well.

Josh