Move / Attach SQL Server database files (.mdf and .ldf)
Posted On: 03-Feb-2018 06:30
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.
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
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
- Right click on the name of the database
- 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' path and mame ...
- Right click on the database 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
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.
In the above discusion we saw how to move sql server files from one location to another. Hope it will helpful.