How to move MDF/NDF for a Replicated Database

Share this Post

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.

http://msdn.microsoft.com/en-us/library/bb522469.aspx
MODIFY FILE ( NAME = logical_file_name, FILENAME = ‘ new_path/os_file_name ‘ )


Share this Post

3 thoughts on “How to move MDF/NDF for a Replicated Database”

  1. 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. alter database sample

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

    Go

    alter database Sample

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

    Go

    Stop Log reader agent (if Replicated)

    alter database Sample

    SET Offline WITH

    ROLLBACK IMMEDIATE

    Move the physical file manually

    alter database Sample

    SET Online

    Start Log reader agent (if Replicated)

Leave a Reply to Ben Thul Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.