September 4, 2008 at 9:02 am
Long story short, a vendor created a database putting both the database and log files on the same drive we have for logs (e: drive). Is this the best way to move the ldf file to its new home? Thanks.
Alter Database ExampleDB
Modify File (Name = 'ExampleDB', FileName = 'd:\datafile\ExampleDB.ldf')
September 4, 2008 at 9:13 am
Not sure about the Alter (it may require a restart of SQL Server), but if your app can afford some down time another way to accomplish the move is to detach the database, move the files to the proper locations, then attach the database.
😎
September 4, 2008 at 9:28 am
Long answer, short: Yes.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 4, 2008 at 12:32 pm
After running the above codes on a VM, I received "The file "ExampleDB" has been modified in the system catalog. The new path will be used the next time the database is started." I took the database offline, moved the ldf file to the new location and successfully brought it online. Is this how it's supposed to be done or is there a way to do it all within Management Studio? Thanks.
September 4, 2008 at 1:11 pm
If you are allowed to use xp_cmdshell you could perform
- your alter
- then put the db offline
- xp_cmdshell 'xcopy "c:\thefile.ldf" "x:\thenewwpath"'
- and alter db online.
With sql2005 you need to sp_configure the cmdshell and shut it down if you enabled it ! (sysadmin priv)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 5, 2008 at 8:02 am
Preston (9/4/2008)
After running the above codes on a VM, I received "The file "ExampleDB" has been modified in the system catalog. The new path will be used the next time the database is started." I took the database offline, moved the ldf file to the new location and successfully brought it online. Is this how it's supposed to be done or is there a way to do it all within Management Studio? Thanks.
Once you've given the alter command, you don't need to actually move the file yourself. You should just need to take the database offline and then back online. Or restart the SQL Server service. Or reboot. Any of those three should do it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 5, 2008 at 8:35 am
GSquared (9/5/2008)
Preston (9/4/2008)
After running the above codes on a VM, I received "The file "ExampleDB" has been modified in the system catalog. The new path will be used the next time the database is started." I took the database offline, moved the ldf file to the new location and successfully brought it online. Is this how it's supposed to be done or is there a way to do it all within Management Studio? Thanks.Once you've given the alter command, you don't need to actually move the file yourself. You should just need to take the database offline and then back online. Or restart the SQL Server service. Or reboot. Any of those three should do it.
I actually tried this and got the message that it cannot find the file.
September 5, 2008 at 9:08 am
Really!?! That's odd.
I moved a database just last week just using the Alter Database commands, and the files ended up where I wanted them. Of course, that was tempdb, which might work differently.
I'll have to test more.
If that's the way it works on user DBs, then definitely detach, move, re-attach. Why do the extra step?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 8, 2008 at 12:01 am
There is no move of the datafiles at alter database time !
With Tempdb, your need the alter database to update the catalog
and you'll need the stop/start to create the tempdb files !
(as it always does when starting the instance)
With all other databases, you'll need to move the file yourself.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 8, 2008 at 8:37 am
ALZDBA (9/8/2008)
There is no move of the datafiles at alter database time !With Tempdb, your need the alter database to update the catalog
and you'll need the stop/start to create the tempdb files !
(as it always does when starting the instance)
With all other databases, you'll need to move the file yourself.
Correct. The tempdb file doesn't get moved. SQL creates a new one, and you have to manually delete the old one.
September 8, 2008 at 8:43 am
GSquared (9/5/2008)
I moved a database just last week just using the Alter Database commands, and the files ended up where I wanted them. Of course, that was tempdb, which might work differently.
With TempDB, SQL will 'relocate' the files for you. With the user databases, you have to do it yourself.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2008 at 11:23 am
GilaMonster (9/8/2008)
GSquared (9/5/2008)
I moved a database just last week just using the Alter Database commands, and the files ended up where I wanted them. Of course, that was tempdb, which might work differently.With TempDB, SQL will 'relocate' the files for you. With the user databases, you have to do it yourself.
Yeah, that's what I belatedly realized.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply