Moving database files to another new location

You can move database data and log files to any other location by following the below steps, these come under Planned Relocation.

1. Check if there is any user connected to DB by executing the below query.
   
   SELECT SPID,LOGINAME,HOSTNAME,PROGRAM_NAME FROM SYS.SYSPROCESSES WHERE DBID=DB_ID('database_name')
   
   If there is any replication agent [Log Reader Agent] is running, stop that agent from Replication Monitor, Kill all other user connection if they are not important as:
   
   KILL @SPID – get spid from above query
   
   
2. Run ALTER DATABASE database_name SET OFFLINE.
3. Move the file to the new location.
4. Run ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path/os_file_name' )
5. Run ALTER DATABASE database_name SET ONLINE.
6. Run replication agent [Log Reader Agent], if stopped.
   

Example:

ALTER DATABASE DB1 SET OFFLINE

Copy log file from the current location to a new location by using Copy and Paste. E.g. you move File1.ldf from c drive to d drive.

ALTER DATABASE database_name MODIFY FILE ( NAME = 'DB1_Log', FILENAME = 'D:\File1.ldf' )

ALTER DATABASE DB1 SET ONLINE