Moving a Replicated SQL Server Database

As many of you will have found, it is not as easy as detaching and attaching a replicated database if you want to move it to a different volume. Replicated database have the Is_Published property set to 1 so cannot be detached.

A way around this is to restrict access and move via the more traditional modify file method. I have tested this on SQL Server 2005.

2 Scenarios confirmed as working – SQL to SQL replication & SQL to Oracle Replication

-- Stop All conectivity to the database
ALTER DATABASE POC_REP_MOVE_TEST set restricted_user with rollback immediate;

ALTER DATABASE POC_REP_MOVE_TEST SET OFFLINE;

--Check Location
SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N’POC_REP_MOVE_TEST’);

--Logically change the destination
ALTER DATABASE POC_REP_MOVE_TEST MODIFY FILE ( NAME = POC_REP_MOVE_TEST, FILENAME = 'L:\POC_REP_MOVE_TEST.mdf' );
ALTER DATABASE POC_REP_MOVE_TEST MODIFY FILE ( NAME = POC_REP_MOVE_TEST_log, FILENAME = 'L:\POC_REP_MOVE_TEST_log.ldf' );

-- Physically move the database files
ALTER DATABASE POC_REP_MOVE_TEST SET ONLINE;

ALTER DATABASE POC_REP_MOVE_TEST set multi_user;

--Check Location
SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'POC_REP_MOVE_TEST');

Advertisements

2 Responses to Moving a Replicated SQL Server Database

  1. Currently it looks like WordPress is the preferred blogging platform out there right
    now. (from what I’ve read) Is that what you’re using on your
    blog?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: