2

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

  • I also had USE MASTER and GO at the beginning of the SQL Statement. – Dennis Jul 30 at 18:02
  • Did not do anything prior to detaching the DB. Detached DB, copied and relocated files, renamed original files, used the SQL Statement to attach, specifying the new file locations. – Dennis Jul 30 at 18:37
  • You should use backup/restore with replace/move, or modify/offline/online, not detach/attach, IMHO. – Aaron Bertrand Jul 30 at 19:00
5

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.

1

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:\SQL\HomeDB\Data\MyAdventureWorks.mdf')
  4. ALTER DATABASE MyAdventureWorks MODIFY FILE (name='MyAdventureWorks_log',filename=N'D:\SQL\HomeDB\Data\MyAdventureWorks_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.

  • Result of step 3 tells me DB does not exist. – Dennis Jul 30 at 19:02
  • you cannot offline a database and then change system catalog. See my answer for correct sequence. – Kin Shah Jul 30 at 19:21
  • Check database logical file names. This should help: USE MyAdventureWorks GO SELECT [Name] AS LogicalName, [filename] AS PhysicalFile FROM sys.sysfiles – osiris_v6 Jul 30 at 19:22
  • I brought the DB back on line with the original file locations. I used your steps and they worked fine until bringing it back on line. I'm getting an OS ERROR of access denied to the files. Checking the permissions I see that existing files for other DBs have MSSQLSERVER and the files I moved do not. I tried adding this account to the files but I can't find the account either locally on my SQL Server or on the domain. – Dennis Aug 1 at 14:27
  • Thanks Osiris.. – Dennis Aug 1 at 20:41
0

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_path\os_file_name' );

  4. Bring database back online

  • I didn't just copy and use the code from MSDOCS without editing for my environment. Brought it back to original state and tried again. Everything is fine until bringing back on line. I'm getting an OS Error of access denied when trying to bring on line. Thanks for posting. – Dennis Aug 1 at 14:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.