Home / server / Questions / 378147
Accepted
ileon
Asked: 2012-04-11 07:20:34 +0800 CST

How to move the files of a replicated database (SQL Server 2008 R2) to a different drive

  • 772

I would appreciate if someone could help me with the following problem:

We use two SQL Server 2008 R2 databases under transactional replication: transactional publication with updatable subscriptions. because we run out of disk space we need to move the database files into a new drive. But I don't want to break the replication.

What I'm looking for are the required steps that will help me to move the files to the new drive.

Thanks

sql-server sql-server-2005 sql-server-2008 replication transactional-replication

2 Answers

  1. Best Answer
    1. alter database [publisher] modify file (name = 'logical_name', filename = 'new_path')
    2. alter database [publisher] set offline
    3. physically move the files from the old location to the new
    4. alter database [publisher] set online

    Keep in mind that the logreader agent will hold a connection open to the publisher. You can stop the agent w/o breaking anything. Just remember to start it back up again. :)

    • 2

  2. You need to make sure there are no other active SQL sessions to the publisher database or setting it to offline will fail.

    -GFT

    • 1