Sometimes, we need to move the user database files to a different location. There are several ways to do it,
One way is to use sp_detach_db and copy the files and use sp_attach_db.
sp_detach_db cannot be used, if the database is being replicated. Also,sp_attach_db is not recommended anymore by Microsoft.
One other way to move database files to new location is:
USE [TESTA]
GO
Select * from sys.database_files--This gives the current location of the files
GO
USE [master]
GO
Alter database TestA
Modify File (Name = 'TestA',filename= N'E:\Data\Data\TestA.mdf')
GO
Alter Database TestA
Modify File (Name = 'TestA_Log',FileName = N'E:\Data\Data\TestA_Log.Ldf')
GO
ALTER DATABASE TESTA Set OFFLNE
---Copy the physical files to the new location
ALTER DATABASE TestA Set ONLINE
If you are moving the files to a new location, the path should already exists(I.e.I moved the files to new location,”E:Data\Data”. This path along with the folder structure should already exists).
Also, This method works for replicated databases as well. It is better to stop the replication jobs while doing this operation and enable them again.
SQL Server Repository 