How to change the path of a database in SQL Server

Who I am
Lluis Enric Mayans
@lluisenricmayans
Author and references

A few days ago I had to necessarily change the path of a database in SQL Server 2005 from unit C: to another unit for reasons related to limited space.

In this way I freed quite a lot of gigabytes from the operating system drive, on which I had actually installed SQL Server, and, at the same time, I made SQL Server point to a database physically located on another drive. .



So here are the steps you need to take:

1) right-click on the database concerned and select the item in the pop-up menu Detach. Detach is the fastest method for disconnect the database from SQL Server and remove it from the list of databases managed by the server, so before doing this operation if you are interested in keeping the same name given to the database before the detach, you should write it down somewhere.

2) Immediately after the Detach disattivate Sql Server Agent clicking on it with the right mouse button and select the item STOP.

3) Now you can go to the SQL Server path, in my case in C: PROGRAM FilesMICROSOFT SQL SErverMSSQL.1MSSQLDATA, and get the files MDF e LDF (LDF files are so called Log files), cut and paste them

in the new unit of your choice.

4) After this Cut / Paste operation, right click on the item Databases and select Attach (which will bring your database back to life), a new window will open where you have the option to enter the name of a new database and add the MDF file fetching it in the new drive where you previously pasted it (the LDF file is automatically added to the MDF file load).



5) Now you just have to Re-enable Sql Server Agent clicking on it with the right mouse button and selecting the item START.

add a comment of How to change the path of a database in SQL Server
Comment sent successfully! We will review it in the next few hours.