Take the 2-minute tour ×
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.

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 stackoverflow.com 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
2  
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

3 Answers 3

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 commad:

USE master; --do this all from the master
ALTER DATABASE foo
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)

ALTER DATABASE foo SET OFFLINE WITH ROLLBACK IMMEDIATE;

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

ALTER DATABASE foo SET ONLINE;

You can see this described in more detail here.

share|improve this answer

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 use database copy wizard with backup and restore mode. Right click on db Task Copy database

share|improve this answer

Your Answer

 
discard

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