@manhnguyenv

Welcome to my blog!

Move Database Files to Another Location

June 2, 2017 00:13

How do I move database files 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
ALTER DATABASE [AdventureWorks2016CTP3]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

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


-- Move MDF File from C:\MSSQL\ to D:\MSSQL\ and COPY Permission

-- Re-Attached DB
CREATE DATABASE [AdventureWorks2016CTP3] ON
( FILENAME = N'd:\MSSQL\Data\AdventureWorks2016CTP3_Data.mdf' ),
( FILENAME = N'd:\MSSQL\Data\AdventureWorks2016CTP3_Log.ldf' )
FOR ATTACH
GO

 

Refer:

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

https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-a-database-using-detach-and-attach-transact-sql

Categories

Recent posts