Microsoft SQL Server forum - Moving the distribution database

Author

Moving the distribution database

joshsackett

2005-08-03, 11:24 am

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

2005-08-03, 8:24 pm

joshsackett (joshsackett@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\distri
bution.MDF')
> ALTER DATABASE distribution MODIFY FILE
> (name = distmodel_log, filename = 'C:\DISTMOVED\distri
bution.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, esquel@sommarskog.se

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

joshsackett

2005-08-03, 8:24 pm

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

Sponsored Links