| || |
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.
It is very simple to move them is fairly simple. Follow following steps:
I) Excute ALTER DATABASE command:
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
III) Move/Copy the files to the new location
IV) Bring the database 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
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.
Active User (1)