You can move database data and log files to any other location by following the below steps, these come under Planned Relocation.
- 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
- Run ALTER DATABASE database_name SET OFFLINE.
- Move the file to the new location.
- Run ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path/os_file_name' )
- Run ALTER DATABASE database_name SET ONLINE.
- Run replication agent [Log Reader Agent], if stopped.
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