Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a database and want to move the .mdf and .ldf files to another location. But I do not want to stop the MSSQLSERVER service, and I do not want to export to another server.

How can I do this?

share|improve this question

migrated from Oct 22 '13 at 22:28

This question came from our site for professional and enthusiast programmers.

Do you want to copy the files, or do you want to do a backup? – Chris Diver Oct 22 '13 at 12:28
I want to copy that files, I tried to use a backup but i don't think is ok. – user2645263 Oct 22 '13 at 12:31
Why do you think a backup is not okay? It depends what you are trying to achieve. – Chris Diver Oct 22 '13 at 13:04

You don't have to stop the SQL Server service to move database files, but you do have to take the specific database offline. This is because you can't move files while they're being accessed and taking the database offline stops the files from being used by the SQL Server application.

The process to move them is fairly simple. Detach/Attach was already described, but it is not nearly this complex.

Change the file locations with an ALTER DATABASE command:

USE master; --do this all from the master
MODIFY FILE (name='DB_Data1'
             ,filename='X:\NewDBFile\DB_Data1.mdf'); --Filename is new location

Note, you do not need to declare the old location in this command. Changing this path does not take effect immediately, but will be used the next time the database starts up.

Set the database offline

(I use WITH ROLLBACK IMMEDIATE to kick everyone out and rollback all currently open transactions)


Move/Copy the files to the new location

Just copy the files over using your favorite method (Click 'n Drag, XCopy, Copy-Item, Robocopy)

Bring the database online


You can see this described in more detail here.

share|improve this answer
This worked for me. In my case I also had to move the LDF file too using the first command: e.g. USE master; --do this all from the master ALTER DATABASE foo MODIFY FILE (name='DB_Data1_log' ,filename='X:\NewDBFile\DB_Data1_log.ldf'); --Filename is new location – Jubblerbug Jun 26 '15 at 8:01
Also can be used to move the full text index files just by updating the file name to match what you see in the files dialog. – DShook Sep 24 '15 at 20:46
you can find the location of databse files with "SELECT name, physical_name FROM sys.master_files " – Andrew Hill Oct 14 '15 at 5:13

MDF and LDF files are protected and cannot be moved while the server is running.

If you don't mind stop the DB from working, then you can DETACH it then move the files and then ATTACH it.

  • Right click on the name of the DB
  • Select Properties
  • Go to the Files tab
  • Make a note of the Path and FileName of mdf and ldf files. This step is important in case you don't want to end up searching for missing files...
  • Right click on the DB name
  • Select Tasks->Detach
  • Move the files where you want
  • Right click on the DataBases node of your Server
  • Select Attach
  • Click on the Add button
  • Point to the new location.
  • Click OK

You should be ok now. Info about DETACH-ATTACH process can be found here

In the link about DETACH-ATTACH there is a recomendation of using ALTER DATABASE statement if keeping the DB in the same instance of SQL More reference in Move User Databases

If you want to keep it running while moving, then do a BACKUP RESTORE. In the restore process you may define the new location of the db files.

share|improve this answer
You can move a database without stopping the server/instance. – Peter Oct 23 '14 at 0:25
@Peter I never said you have to stop the server/instance. I said stop the DB from working (like bring it offline), not the server. And i only said that so you do not get transactions while in the process of moving. – Giannis Paraskevopoulos Oct 23 '14 at 8:12
Then I find your first sentence ambiguous. – Peter Oct 23 '14 at 8:18
@Peter The bullets analyze the steps of the first sentence. – Giannis Paraskevopoulos Oct 23 '14 at 8:58

You can use database copy wizard with backup and restore mode. Right click on db Task Copy database

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.