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' );
GOOnline Database
ALTER DATABASE YOURDATABASENAMESET ONLINEVerify
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.
Andriy Kravets is writer and experience .NET developer and like .NET for regular development. He likes to build cross-platform libraries/software with .NET.
