Moving distribution database - how? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Moving distribution database - how?

Author

Topic

anxcomp
Starting Member

41 Posts

Posted - 2007-08-08 : 05:55:25

Hello,

I have my distribution database at C drive, I have to move this database to another drive D.

May anybody tell me how do this secure, please.

This production environment so I can't make any mistakes.

--
Regards

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-08 : 13:47:48

Try 'alter database', test it on dev server first.

anxcomp
Starting Member

41 Posts

Posted - 2007-08-08 : 17:03:26

Hi,

Thanks for answer, I received similar answer at groups, to use alter database:

===========================================================================================
1. Run ALTER DATABASE database_name SET OFFLINE.
2. Move the file to the new location.
3. Run ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name,
FILENAME = 'new_path/os_file_name'.
4. Run ALTER DATABASE database_name SET ONLINE.
Reference -
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/89f01b10-5fae-4ed8-b0fb-a4b9f540fd28.htm
===========================================================================================

Only one question is it will be working at distribution database?

I found at groups this:

===========================================================================================
Actually, it is not officially support that
you detach/attach distribution database. Usually it is suggested that
you
completely remove/reconfigure replication if you'd like to move
distribution database to a different place. You can't detach a
database
that participates in Replication. This behavior is by design.

You may want to backup the database, and use "with move" clause to
restore
the database to the place you want as a workaround now.

I've got a couple of replicated databases and I've found myself in
the
position of wanting to move my distribution database to a different
storage
volume. Enterprise manager considers it replicated, so I can't just
detach
it, copy and reattach. Anyone know of a good way to do this?

I think you need to completely remove replication and then start over
from
start.

I thought only one way (unfortunately) is remove replication :(
What are you think?
===========================================================================================

So I'm not sure now? Did somebody tray this method on distribution database?

--
Regards

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-08 : 17:33:31

You need to stop replication agents first.

anxcomp
Starting Member

41 Posts

Posted - 2007-08-09 : 12:28:02

rmiao thank you very much. I've successfully moved distribution database at my server (SQL 2005).I used ALTER DATABASE example.

If this command "ALTER DATABASE database_name SET OFFLINE." will take long time, you should kill any connection to database.

Thanks

--
Regards
anxcomp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources