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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s