Moving MDF and LDF files

Question

Post reply

Moving MDF and LDF files

LearningDBA

SSCertifiable

Points: 6609
More actions
March 15, 2014 at 9:17 am

#284595

I am wondering if someone can help me out.
Scenario:
Two instances (with each one database) on the same server and the database files of both instances on the same drive.
What's the best way to move the mdf and ldf files to different drive and also change the path so both databases use different drives for new transactions.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

ALTER DATABASE with the WITH MOVE clause

Take the database offline

Copy the files to their new location

Bring the DB online.

Test in a dev/test environment first.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 2

I dont think instances matters...

Gail already suggested... 🙂

LearningDBA SSCertifiable Points: 6609 More actions · Answer 3

GilaMonster (3/15/2014)
ALTER DATABASE with the WITH MOVE clause
Take the database offline
Copy the files to their new location
Bring the DB online.
Test in a dev/test environment first.

Unfortunately, we did that in prod :-D. Anyway, we were able to re-attach the DB.

1 question; Do we have to move the tempDB as well?

"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

george sibbald SSC Guru Points: 104210 More actions · Answer 4

you don't HAVE to, if its not causing you amy performance issues, but you may want to to separate out the instances more.

---------------------------------------------------------------------

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Testing in Production. Excellent idea! </sarcasm>

You don't have to move any database. Whether or not you move TempDB depends on why you're moving the other databases.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

LearningDBA SSCertifiable Points: 6609 More actions · Answer 6

We were just moving the databases because we had 2 databases using the same drive. We just wanted to put them on a separate drive, so if 1 runs out of space, it doesn't effect the 2nd one.

"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 7

ALTER DATABASE with the WITH MOVE clause
Take the database offline
Copy the files to their new location
Bring the DB online.
Test in a dev/test environment first.

Gila, Will it work if I do the following ?

Detach database

Move files

Attach from new location.

Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Providing you don't have replication or CDC. It's not the best option though.

Take backups first. Test in dev/test first.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101098 More actions · Answer 9

GilaMonster (3/15/2014)
ALTER DATABASE with the WITH MOVE clause
Take the database offline
Copy the files to their new location
Bring the DB online.
Test in a dev/test environment first.

I'm not aware of "ALTER DATABASE ... WITH MOVE".

Did you mean "ALTER DATABASE ... MODIFY FILE ..."?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".