Moving multiple log files to other drive

SQL Server Administration
1

Dear Experts,
Need a advise.

I have inherited a 2008R2 server,which has around 50 databases.
The guy who setup this initially has put both the data and log files on the same drive and now I am planning to move the Log files to a dedicated drive.
Is there a way I can do this task for all the databases one time?
Manually moving each file is very time consuming.

Thanks,
Javeed

2

I think you are stuck with:

Schedule downtime
ALTER DATABASE MyDatabase SET offline
ALTER DATABASE MyDatabase MODIFY FILE ( NAME = LogicalNameOfLog, FILENAME = "x:\path\LogFilename.ldf")
Physically move the LOG file to new drive
ALTER DATABASE MyDatabase SET online

I'd throw in a full backup, or at least a final LOG backup. after taking the DB offline and before the move (in case it fails for whatever reason)

The whole lot can be scripted ... but its not exactly trivial

3

If it's all on a SAN, there's not any real advantage to moving the files because you can't guarantee that the log files will end up on separate physical spindles from the data files.

4

unless you have good friends in SAN management who owe you a favor!

5

heh... or are afraid of you. :wink: