A lot of times we come across issues when our disk space is consumed and we need to move our database MDF & LDF files from their default location. <\/p>\n\n\n\n
In an earlier version of SQL Server, you have to use Detach and Attach method in order to move database files location. However, it is an old method and you do not have to follow the same method now.<\/p>\n\n\n\n
I use the following steps to move database MDF and LDF files from one location to another.<\/p>\n\n\n\n
Let’s assume the current location of MDF and LDF files are in the C drive<\/strong> and you want to move these files to the D drive<\/strong>.<\/p>\n\n\n\n Open SQL Management Studio and open the query window and follow the below steps. replace “YOURDATABASENAME ” with the name of the actual database.<\/p>\n\n\n\n use the following SQL to get the current location of database files<\/p>\n\n\n\n Since the database is now offline, you can move MDF and LDF files to a new location with the xCopy command in the command prompt or manually copy\/paste using explorer.<\/p>\n\n\n\n You can use the following command to update MDF and LDF file location in the database<\/p>\n\n\n\n <\/p>\n\n\n\n <\/p>\n\n\n\n <\/p>\n","protected":false},"excerpt":{"rendered":" A lot of times we come across issues when our disk space is consumed and we need to move our database MDF & LDF files…<\/p>\n","protected":false},"author":1,"featured_media":663,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[84],"tags":[32,23,17],"class_list":["post-734","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql","tag-developer","tag-development","tag-sql-server"],"yoast_head":"\nGet Current Location<\/h2>\n\n\n\n
SELECT name, physical_name AS CurrentLocation, state_desc\nFROM sys.master_files\nWHERE database_id = DB_ID(N'YOURDATABASENAME ');<\/code><\/pre>\n\n\n\nOffline Database<\/h2>\n\n\n\n
ALTER DATABASE YOURDATABASENAME SET OFFLINE;<\/code><\/pre>\n\n\n\nMove MDF & LDF files<\/h2>\n\n\n\n
Update File location in the database<\/h2>\n\n\n\n
ALTER DATABASE YOURDATABASENAME \nMODIFY FILE ( NAME = YOURDATABASENAME_Data, FILENAME = 'D:\\data\\YOURDATABASENAME.mdf' );\nALTER DATABASE YOURDATABASENAME \nMODIFY FILE ( NAME = YOURDATABASENAME_Logs, FILENAME = 'D:\\data\\YOURDATABASENAME.ldf' );\nGO<\/code><\/pre>\n\n\n\nOnline Database<\/h2>\n\n\n\n
ALTER DATABASE YOURDATABASENAMESET ONLINE<\/code><\/pre>\n\n\n\nVerify<\/h2>\n\n\n\n
SELECT name, physical_name AS CurrentLocation, state_desc\nFROM sys.master_files\nWHERE database_id = DB_ID(N'YOURDATABASENAME ');<\/code><\/pre>\n\n\n\n