I wanted to move the MDF/LDF file locations for a DB. From what I read I found I should use Detach/Attach and not just take the DB off line and relocate the files. I did just that and used the below to Re-Attach the DB.
CREATE DATABASE MyAdventureWorks
ON (FILENAME = 'C:\MySQLServer\AdventureWorks2012_Data.mdf'),
(FILENAME = 'C:\MySQLServer\AdventureWorks2012_Log.ldf')
FOR ATTACH;
GO
Of course using my new file locations which used two separate volumes and not the location moving from.
BUUUT, all this did was put the files back on the original volume that I did not specify. It also put the DB in a READ ONLY state.
Any help would be appreciated.
Thanks
SQL Server 2016
Best Answer
The correct (and preferred) way in my opinion should be
alter database....MODIFY FILE
- This will update system catalog.I have written a script to rename a database - db name and the files -logical and physical and you can use that logic to adjust it as per your requirements.
Note: Before doing any detach/attach, you should take a full backup of the database. See Bad Habits: Using MDF/LDF Files .. from Aaron Bertrand.