How to move MDF/NDF for a Replicated Database
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 ‘ )
Tags: Best Practices
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'.
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)
Hi..how to go about moving db's of they are large is size i.e. around 1TB?
I don’t even know how I stopped up right here,
but I believed this publish was once great. I do not know who you’re but certainly you are going to a well-known blogger should you are not already.
Cheers!