SQL Server DBA : Move (database) log files to another drive

I have recently encountered a situation where I had to move the log files of a database to a different drive.

Here is the code to move the file to a different drive. The database will not be accessible while performing this task.

use master
go
-- Set database to single user mode
alter database TestDB
set single_user
go
-- Detach the database
exec sp_detach_db 'TestDB'
go
-- Attach the database
exec sp_attach_DB @dbname = 'TestDB',
@filename1 = 'H:\Data\TestDB.mdf',
@filename2 = 'H:\Data\TestDB_0.ndf',
@filename3 = 'I:\Log\TestDB_1.ldf'
go
alter database TestDB
set multi_user
go

SQL Server : Move database files to another location

There may be situations where you need to move database files from one drive to another. You can move database files of a non-system database as follows:

1. Right click on the database and select Tasks –> Take Offline
2. Once the database is offline, detach the database by right click on the database, select Tasks –> Detach
3. Copy the data and log files to the new location.
4. Right click on the Databases folder and select Tasks –> Attach
5. In the Attach Databases window click on Add button and browse and select the data file from the new location and click OK

Follow the steps below to move TempDB to a new directory:

1. Run the query below with the new location
   
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TempDB\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TempDB\templog.ldf');
GO

2. Restart the SQL Server
3. SQL Server will then create tempDB files in the new location
4. Delete the old files

You can check the location of the database files with the query below:


SELECT name, physical_name, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO