| Author |
Topic |
|
sqlserverdbam
Yak Posting Veteran
54 Posts |
 Posted - 2010-04-07 : 17:00:51
|
| Hi,How to Move log file from one location to another location?I know dettach and atatch is one way. Is there any other way I can do?Thanks, |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-07 : 18:20:15
|
| Tara, as an aside, what happens when you detach the log, and attempt to update the data before reattaching it? Does it block? Also, do you have to put the db into single user mode before detaching the log?There are 10 types of people in the world, those that understand binary, and those that don't. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-07 : 19:47:00
|
quote:
Originally posted by tkizer
If the database is detached, then it is inaccessible to all queries. It is completely down in that state.You do not have to put the database into any mode to detach it. You do need to disconnect the users though.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Sorry, I had the idea that you guys were talking about detaching the log file only. Sorry for the silly question.  There are 10 types of people in the world, those that understand binary, and those that don't. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-08 : 03:23:45
|
| Hmmm ... we have a house rule here never to detach a database unless it is backed up ... just in case it won't reattach. Thus for us Restore is not always a poor-relation to detach/re-attach - although in this case it would be! If moving to a new machine then we would Restore (backup file will be smaller to XFer, and our Restore process will consider logical names, logons/permissions, etc.) |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-04-08 : 12:42:33
|
| You can also use the ALTER DATABASE, set Offline, move file, set online method. No detach necessary.--Gail ShawSQL Server MVP |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Peter99
Constraint Violating Yak Guru
498 Posts |
Posted - 2010-04-08 : 13:58:38
|
| GilaMonster, can you explain how you can take database offline, move file(s) and then take database online? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-08 : 16:14:05
|
quote:
Originally posted by tkizer
Kristen, I'm not saying BACKUP is silly just the RESTORE is. Extra backups are never silly.
Yes, I agree, in this case (moving the LDF to another location on the same server) RESTORE is overkill / much more effort.However, if the database was being moved to a different server, and assuming you take a "safety" backup before doing DETACH, then I think ATTACH and RESTORE are much-of-a-muchness, and RESTORE route is more likely to consider Logical name changes, user logon resync, and so on. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-04-09 : 15:19:58
|
quote:
Originally posted by Peter99
GilaMonster, can you explain how you can take database offline, move file(s) and then take database online?
Use the ALTER DATABASE statement and specify the MOVE clause to change the database metadata. Once you've done that, take the database offline, move the files to the new location (the location that you specified in the ALTER DATABASE, then bring the database back online.--Gail ShawSQL Server MVP |
|
|
|
Peter99
Constraint Violating Yak Guru
498 Posts |
Posted - 2010-04-09 : 17:18:39
|
| Then what is difference in detach/attach and alter database move, in both cases database will not available to users and you have to move files manually. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-04-09 : 17:55:15
|
| Alter database doesn't remove cross DB permission chaining, it doesn't remove the database from SQL (so database ID won't change), may have some implication with service broker.Any time that you're changing file locations, the DB has to be down for the move, there's no way to move a file with the DB online and accessible. The Alter Database is just a different way of doing the move.--Gail ShawSQL Server MVP |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Move log file from one location to another
Tara Kizer