I have a database and want to move the
.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?
migrated from stackoverflow.com Oct 22 '13 at 22:28
This question came from our site for professional and enthusiast programmers.
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
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
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.
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
You should be ok now. Info about
In the link about
If you want to keep it running while moving, then do a
You can use database copy wizard with backup and restore mode. Right click on db Task Copy database
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.