SQL SERVER – Move Database Files MDF and LDF to Another Location

SQL SERVER – Move Database Files MDF and LDF to Another Location

Let us assume we have two folders loc1 and loc2. We want to move database files from loc1 to loc2.




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





USE MASTER;
GO

ALTER DATABASE TestDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
-- Detach DB
EXEC MASTER.dbo.sp_detach_db @dbname = N'TestDB'
GO




Now move the files from loc1 to loc2. You can now reattach the files with new locations.

-- Move MDF File from Loc1 to Loc 2
 -- Re-Attached DB
CREATE DATABASE [TestDB] ON
( FILENAME = N'F:\loc2\TestDB.mdf' ),
 ( FILENAME = N'F:\loc2\TestDB_log.ldf' )
FOR ATTACH
 GO

Well, we are done. There is little warning here for you: If you do ROLLBACK IMMEDIATE you may terminate your active transactions so do not use it randomly. Do it if you are confident that they are not needed or due to any reason there is a connection to the database which you are not able to kill manually after review.


http://blog.sqlauthority.com/2012/10/28/sql-server-move-database-files-mdf-and-ldf-to-another-location/