How to Move SQL Server MDF and LDF Files Location

We frequently encounter problems when our disk space is full and we have to relocate our database’s MDF and LDF files from their original locations.

To change the location of database files in a previous version of SQL Server, you had to use the Detach and Attach method. But that’s an outdated approach, so you don’t have to use it anymore.

To transfer database MDF and LDF files from one place to another, I use the procedures listed below.

Assume that the MDF and LDF files are currently on the C drive and that you wish to transfer them to the D drive.

Launch SQL Management Studio, click the query window, and then take the actions listed below. swap out “YOURDATABASENAME” for the database’s real name.

Get Current Location

To find the current location of database files, use the SQL below.

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'YOURDATABASENAME ');

Offline Database

ALTER DATABASE YOURDATABASENAME SET OFFLINE;

Move MDF & LDF files

Now that the database is offline, you can use the xCopy command in the command prompt to move MDF and LDF files to a new location, or you can copy and paste the files by hand using Explorer.

Update File location in the database

To change the location of MDF and LDF files in the database, use the following command.

ALTER DATABASE YOURDATABASENAME 
MODIFY FILE ( NAME = YOURDATABASENAME_Data, FILENAME = 'D:\data\YOURDATABASENAME.mdf' );
ALTER DATABASE YOURDATABASENAME 
MODIFY FILE ( NAME = YOURDATABASENAME_Logs, FILENAME = 'D:\data\YOURDATABASENAME.ldf' );
GO

Online Database

ALTER DATABASE YOURDATABASENAMESET ONLINE

Verify

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'YOURDATABASENAME ');

Conclusion

In above article, you have learned how to move mdf and ldf file to different location.

Whether you’re a seasoned web developer, a personal blogger, or a business owner, we’ve kept you in mind when designing our hosting services.

We provide everything you need, from developer-made tools like Git integration and access managers for user management to basic features like a free domain name, unlimited bandwidth, and free SSL.

Make the switch to ASPHostPortal right now to see the difference for yourself.