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

replicationsql serversql-server-2005sql-server-2008transactional-replication

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

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. :)