move the data(.mdf) and log(.ldf) files

Question

Post reply

move the data(.mdf) and log(.ldf) files

charipg

SSCrazy Eights

Points: 9888
More actions
September 24, 2009 at 7:50 pm

#136307

How to move the data(.mdf) and log(.ldf) files to other drive.

Viewing 12 posts - 1 through 11 (of 11 total)

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

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

Hope you want to move MDF and LDF s of user databases.

-- Take Database offline

Alter Database TestmoveFile Set Offline

GO

-- Move the mdf and ldf files to new location using dos command or Windows GUI

-- Alter the database file path using Alter Database command

Alter Database TestmoveFile

Modify File(Name='MDFLogicalName',FileName='c:\TestmoveFile.mdf')

Go

Alter Database TestmoveFile

Modify File(Name='LDFLogicalName',FileName='c:\TestmoveFile.ldf')

Go

-- Set the database Online and check the file path

Alter database TestMoveFile Set Online

GO

Select * From Sys.master_files where database_id=db_id('TestmoveFile')

t2sqldba SSChasing Mays Points: 625 More actions · Answer 2

1) Lets assume that you are targeting the user database name TargetDB

2) Open the SQl server management studio

3) Click on the + symbol on the left hand side of the word database

4) Right click on the database TargetDB

4.1) Select the properties to check the current location of the .mdf and .ndf files of the TargetDB datbase,make a note of it.

4.2) Again right click on the database TargetDB.

5) Select the option "Take database Offline"

6) Now you can copy the TargetDB.mdf and TargetDB.ndf files to a different drive of your choice depending upon the space availability.

10) After after that attach the .mdf and .ndf files to the TargetDB database.

11) Now make it online........:-)

free_mascot One Orange Chip Points: 27169 More actions · Answer 3

If database is not production. You can also use backup and restore with move option.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

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

If database is not production. You can also use backup and restore with move option.

True. But that will leave you with 2 databases unnecessarily. The queston was to to move the data(.mdf) and log(.ldf) files to other drive. Hence you can follow the solution given above. You can do it using SSMS or scripts. I always prefer scripts anyways.

free_mascot One Orange Chip Points: 27169 More actions · Answer 5

San,

There are multiple way of doing the same things...

You can easily delete the unwanted database.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Silverfox SSC-Insane Points: 23150 More actions · Answer 6

free_mascot (9/25/2009)
San,
There are multiple way of doing the same things...
You can easily delete the unwanted database.

Very true Mascot, thanks for pointing that out 😛

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

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

San-847017 (9/25/2009)
If database is not production. You can also use backup and restore with move option.
True. But that will leave you with 2 databases unnecessarily. The queston was to to move the data(.mdf) and log(.ldf) files to other drive. Hence you can follow the solution given above. You can do it using SSMS or scripts. I always prefer scripts anyways.

not if you use the 'replace' option as well.........................

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

Sergey Vavinskiy SSCarpal Tunnel Points: 4469 More actions · Answer 8

I want to put my 2 cents since this discussion moved from "How to move files" to "How to copy/move database".

In this case we can also use a Copy Database Wizard, where we are getting some addional options:

1. Database can stay online during this process (if we use SMO method)

2. Database can be copied or moved

3. You can do multiple databases

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

There are multiple way of doing the same things...

I agree. You are absolutely right.

I just gave the solution which I thouhgt the most suitable for that particular question.

You can easily delete the unwanted database.

hmmmm..even that's true..But what if there's no sufficient space to accomodate 2 databases.? Moving will not require any additional space.

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

San, you are correct, the alter database..modify file is the best option for moving database files as it is simple, quick and maintains the database owner and dbid.

please see my earlier post about the backup\restore method though...........

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

espanolanthony SSCarpal Tunnel Points: 4204 More actions · Answer 11

just right click the DB select --task---detach

move the mdf and ldf files

now right click on database--attach and now select mdf and ldf from the new location that you moved to.

that's all