Best practices for changing Database Directories for SQL Server 2008 R2

I have a  Server 2008 R2 server running SQL Server 2008 R2 and I need to change the Directory for the LDF and MDF files. The original install from a prior Sys Admin had everything installed on the C Drive 5 years ago. This is a Virtual Server on VMWare 5.5.  I know I can create new partitions but I'm looking for best advice to change the directories without causing intense excruciating pain on myself and my end users! I created separate directories on a SAN for the DATA and one for Log files.
Any advice is appreciated. Seems that a lot of online posts offer different scenarios. Let me know what additional info you need. Thanks
kentpriceAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
Best way to increase performance is to have multiple disk controllers. I am not sure in your case whether it is possible to have a couple of other drives from different SAN;  Creating separate drives on the same disk controller wont usually increase the performance.
kentpriceAuthor Commented:
Thank you Aneesh. What I'm looking for is not so much performance but a good "how to"in changing the database directory. Current SQL Data directory for Database files is C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA and I want to change it to another partition. What is the best way to move the Databases to another partition?
Scott PletcherSenior DBACommented:
For user dbs:
1) Change the file location in SQL's metadata (in master) to the new location:
ALTER DATABASE db1 MODIFY FILE ( NAME = <logical_file_name>, FILENAME = 'x:\new\path\to\db\file\db1.mdf' )
--... one ALTER statement per filegroup and log file in the db
2) Take the db offline:
ALTER DATABASE db1 SET OFFLINE
3) Copy (or move, if you're really really brave) the db files from the old location to the new one
4) Bring the db back online:
ALTER DATABASE db1 SET ONLINE

Whatever you do, do NOT detach the dbs!  That is an obsolete method of changing file locations on the same server.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
It's similar for system dbs, but of course instead of taking the dbs offline you have to stop and start SQL Server.  For the master db, you have to change the file location in SQL's startup parameters, not (just) with an ALTER statement.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.