How to move MDF/NDF for a Replicated Database

How to move MDF/NDF for a Replicated Database

Chris Skorlinski
Microsoft SQL Server Escalation Services

Here is another customer question about Replication I thought worth sharing.

I am using SQL Server 2008 64 bits. I have to change the location of the data file (i.e. MDF file). I was planning to  detach and attach. DB is also using Transactional Replication so I can’t detach the DB. Is there any way I can change the location of data file (i.e. MDF file) while DB is using transactional replication?

Instead of detach and attach WITH MOVE to change a database MDF/NDF location you can use the ALTER DATABASE  <db name> MODIFY FILE.
MODIFY FILE ( NAME = logical_file_name, FILENAME = ‘ new_path/os_file_name ‘ )

Comments (3)

  1. Ben Thul says:

    It should be noted that that doesn't actually move the physical file, but it modifies the system catalogs to look in the new location the next time the database is started.  In order to move the physical file, you can do 'alter database <db_name> set offline', move the file, and then 'alter database <db_name> set online'.

  2. Masud says:

    alter database sample

    modify file (name = 'Sample', filename = 'F:SQL_Datasample.mdf')


    alter database Sample

    modify file (name = 'Sample_log', filename = 'D:SQL_Logssample_log.ldf')


    Stop Log reader agent (if Replicated)

    alter database Sample

    SET Offline WITH


    Move the physical file manually

    alter database Sample

    SET Online

    Start Log reader agent (if Replicated)

  3. Minesh says: to go about moving db's of they are large is size i.e. around 1TB?