Tuesday, October 15, 2013

Move Database Files MDF and LDF to Another Location

USE MASTER
GO
-- Take database in single user mode -- if you are facing errors
-- This may terminate your active transactions for database

--e.g
--If the old location are
--V:\SQLDB\DATA\Test_DB123.MDF
-- L:\SQLDB\LOG\Test_DB_log123.LDF


ALTER DATABASE Test_DB123
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO


-- Detach DB
EXEC MASTER.dbo.sp_detach_db @dbname = N'Test_DB123'
GO


-- Move MDF and LDF Files from Old to New
-- Re-Attached DB

CREATE DATABASE [Test_DB123] ON
( FILENAME = N'D:\MSSQL\DATA\Test_DB123.mdf' ),
( FILENAME = N':E\MSSQL\LOG\Test_DB_log123.ldf' )
FOR ATTACH
GO

* Try if it works for you.