Move database files .mdf & .ldf to different physical location
Here is a brief and simple way to move your Data files from one location to another.
This method involves using SSMS (SQL Server Management Studio)
1- Open SSMS and locate to database you want to change Data file directory
2- Remember/Note the current Data file directory path. You can do this by Right Clicking on the database and choose Properties. Then, from the left side menu, click on Files to see the Path of your current Data file directory path. Note it down or click on Control + C to copy the path name.
3- Detach Database by Right clicking on the database and choosing >> Tasks >> Detach
4- A new window will appear. Click on Drop Connections check box and then click OK.
5- Using the windows explorer, go to your Data file directory which you copied or wrote down in step 2:
6- Copy/Move the Data File (.MDF) and the Transaction Log file (.LDF) to your new physical location. Note that I always recommend copying first instead of moving in case the file gets corrupted during the move, you will still have the original one to go back to.
7- Once you copied the two files, click on Database(s) main folder and choose Attach. Then, from the new opened window, click on Add:
8- Navigate to the new path where files are copied and choose the .mdf file and click on OK:
9- Now you can see that both the Data File (.MDF) and Transaction Log File (.LDF) are shown in the new location. Click OK and you are done (You need to refresh the Databases for the new attached database to appear again).
To improve the quality of this user-guide, please help me with some feedback or by simply commenting below.
This method involves using SSMS (SQL Server Management Studio)
1- Open SSMS and locate to database you want to change Data file directory
2- Remember/Note the current Data file directory path. You can do this by Right Clicking on the database and choose Properties. Then, from the left side menu, click on Files to see the Path of your current Data file directory path. Note it down or click on Control + C to copy the path name.
3- Detach Database by Right clicking on the database and choosing >> Tasks >> Detach
4- A new window will appear. Click on Drop Connections check box and then click OK.
5- Using the windows explorer, go to your Data file directory which you copied or wrote down in step 2:
6- Copy/Move the Data File (.MDF) and the Transaction Log file (.LDF) to your new physical location. Note that I always recommend copying first instead of moving in case the file gets corrupted during the move, you will still have the original one to go back to.
7- Once you copied the two files, click on Database(s) main folder and choose Attach. Then, from the new opened window, click on Add:
8- Navigate to the new path where files are copied and choose the .mdf file and click on OK:
9- Now you can see that both the Data File (.MDF) and Transaction Log File (.LDF) are shown in the new location. Click OK and you are done (You need to refresh the Databases for the new attached database to appear again).
To improve the quality of this user-guide, please help me with some feedback or by simply commenting below.
No comments:
Post a Comment