patternsqlModerate
Move log file without taking database offline
Viewed 0 times
withoutfilelogmovedatabaseofflinetaking
Problem
I need to move a database log file to a new partition without taking the database offline.
The normal way of doing this would be to detach the DB, move the log file then reattach the db.
Is it possible to do this without taking the database itself offline and if so how?
The normal way of doing this would be to detach the DB, move the log file then reattach the db.
Is it possible to do this without taking the database itself offline and if so how?
Solution
There is no way to do this with an online database.
When you move a database file (
The file "YourFile" has been modified in the system catalog. The new path will be used the next time the database is started.
The normal way of doing this would be to detach the DB, move the log file then reattach the db.
That's not the "normal" or accepted way I would do it. To move database files, I do the following:
See this reference on TechNet: Move User Databases
When you move a database file (
ALTER DATABASE ... MODIFY FILE), you even get the following message:The file "YourFile" has been modified in the system catalog. The new path will be used the next time the database is started.
The normal way of doing this would be to detach the DB, move the log file then reattach the db.
That's not the "normal" or accepted way I would do it. To move database files, I do the following:
- Run an ALTER DATABASE command to change the location of the file(s)
- Take the database offline
- Physically move the file(s) to the new location specified in step #1
- Bring database online
See this reference on TechNet: Move User Databases
Context
StackExchange Database Administrators Q#64585, answer score: 16
Revisions (0)
No revisions yet.