Mdthbs

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:MySQLServerAdventureWorks2012_Data.mdf'),  

    (FILENAME = 'C:MySQLServerAdventureWorks2012_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

The correct (and preferred) way in my opinion should be

1. Use alter database....MODIFY FILE - This will update system catalog.


2. Offline the database


3. Physically move the files to the new location that you specified in step 1.


4. Online the database

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.

Usually the steps are:

1. ALTER DATABASE MyAdventureWorks SET OFFLINE WITH ROLLBACK IMMEDIATE




2. Move the files to the new location



3. ALTER DATABASE MyAdventureWorks MODIFY FILE (name='MyAdventureWorks',filename=N'D:SQLHomeDBDataMyAdventureWorks.mdf')


4. ALTER DATABASE MyAdventureWorks MODIFY FILE (name='MyAdventureWorks_log',filename=N'D:SQLHomeDBDataMyAdventureWorks_log.ldf')


5. ALTER DATABASE MyAdventureWorks SET ONLINE

Just change the path to the new location and replace if needed name='MyAdventureWorks', and name='MyAdventureWorks_log', as they are the database logical files names.

Check out this reference.

The code you pasted in your question looks like you just copied/pasted the whole statement from the docs. You indicate later that you used "new file locations", but it's unclear if you actually did everything you needed to, especially since what you're doing doesn't seem to be working. Try posting your exact code, with specifics as much as possible.

Microsoft's SQL Docs do cover this topic, as I'm sure you know.

In particular, though, they specifically offer ALTER DATABASE as the standard intra-DB relocation method:

In SQL Server, you can move the data, log, and full-text catalog files
of a user database to a new location by specifying the new file
location in the FILENAME clause of the ALTER DATABASE statement. This
method applies to moving database files within the same instance SQL
Server. To move a database to another instance of SQL Server or to
another server, use backup and restore or detach and attach
operations.

It sounds like you're NOT moving between instances or servers, so ALTER DATABASE should suffice - anything else is just overkill.

To re-summarize the docs:

1. Take database offline




2. Move files to new location




3. Run SQL statement(s) telling SQL server the new file location ex. ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_pathos_file_name' );




4. Bring database back online