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\d istribution.MDF ')
ALTER DATABASE distribution MODIFY FILE
(name = distmodel_log, filename = 'C:\DISTMOVED\d istribution.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]

Re: Moving the distribution database

joshsackett (joshsackett@gm ail.com) writes:[color=blue]
> 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\d istribution.MDF ')
> ALTER DATABASE distribution MODIFY FILE
> (name = distmodel_log, filename = 'C:\DISTMOVED\d istribution.LDF ')
>
> But I get -
> Server: Msg 5037, Level 16, State 1, Line 1
> MODIFY FILE failed. Do not specify physical name.[/color]

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.publi c.sqlserver.rep lication, if I were
you.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[joshsackett]

Re: Moving the distribution database

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