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. Join them; it only takes a minute:

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

I am not sure that this is the best way (I would welcome any comments to tell me how it is not), but it's very simple (and quick if you have a small database):

First, backup the database to a .bak file. Then, restore the database from the same .bak file, choosing the new .mdf and .ldf file locations under file options for the restore task.

I wouldn't do it in a production environment ouside of a maintenance window, as you cannot access the database during the restore. Other methods I've seen above would have similar drawbacks though. After the restore task is done, you don't have to delete the old file. It's automatically done.

share|improve this answer
This scenario has some drawbacks. When restoring, the original DB must be overwritten or renamed. For large databases, the method introduces serious IO overhead. Moving files as described in detach-attach or alter db methods is way faster. If the files are moved within the same NTFS partition, it's a metadata operation only. – vonPryz Jul 1 at 11:47
Thank you for your comment, @vonPryz. I will use the detach-attach method for the larger databases. – Ali Jul 1 at 12:17
But detach-attach may, if something goes wrong, result in an unusable database file. – RLF Jul 1 at 15:22
@RLF What sort of things were you thinking could go wrong? What is your own preferred method? – Ali Jul 1 at 15:51
@Ali - Backup and Restore. May take longer, but is generally a safer path. See Aaron Bertrand's analysis at: Also: – RLF Jul 1 at 17:31

Your Answer


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