Move / Attach SQL Server database files (.mdf and .ldf)

Introduction

Sometime we need/want to move the .mdf and .ldf files to another location. And we do not want to stop the MSSQLSERVER service, and we do not want to export to another server. In this article we will try to attach or move the SQL Server files i.e. .mdf and .ldf file.

Detail

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

• SQL Command
• SQL Server Management Studio

SQL DATABASE Command

It is very simple to move them is fairly simple. Follow following steps:

I) Excute ALTER DATABASE command:

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

Go

ALTER DATABASE foo
MODIFY FILE (name='DB_Name1',filename='X:\NewDBFile\DB_Name2.ldf'); --Filename is new location

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

II) Set the database offline

ALTER DATABASE foo SET OFFLINE WITH ROLLBACK IMMEDIATE;
--(WITH ROLLBACK IMMEDIATE to kick everyone out and rollback all currently open transactions)

III) Move/Copy the files to the new location
 Just copy the files.

IV) Bring the database online

ALTER DATABASE foo SET ONLINE;

SQL Server Management Studio

.mdf and .ldf files are protected and cannot be moved while the database is online. We can DETACH it, move the files and then ATTACH it. Try following steps

1. Right click on the name of the database
2. Select Properties
3. Go to the Files tab
4. 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' path and mame ...
5. Right click on the database name
6. Select Tasks -> Detach
7. Move the files where you want
8. Right click on the Databases node of your server
9. Select Attach
10. Click on the Add button
11. Point to the new location
12. Click OK

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

Note: SQL Server Management studio open Run as Administrator.

Conclusion

In the above discusion we saw how to move sql server files from one location to another. Hope it will helpful.