move db files to another directory

Question

move db files to another directory

sqlfriend

SSC Guru

Points: 52769
More actions
December 4, 2012 at 6:16 pm

#285726

I would like to move database files to another drive.
I used sql like below:
ALTER DATABASE Z_20090501_coreSPSS
MODIFY FILE ( NAME = coreSPSS,
FILENAME = 'G:\DetachedArchiveDatabase\Z_20090501_coreSPSS.mdf');
GO
ALTER DATABASE Z_20090501_coreSPSS
MODIFY FILE ( NAME = coreSPSS_log,
FILENAME = 'G:\DetachedArchiveDatabase\Z_20090501_coreSPSS_log.ldf');
GO
The file "coreSPSS" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "coreSPSS_log" has been modified in the system catalog. The new path will be used the next time the database is started.
But when I look at the G directory, the files are not there
Why is that?
Thanks

Viewing 15 posts - 1 through 15 (of 15 total)

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

Leo.Miller SSChampion Points: 13046 More actions · Answer 1

This only moves the logical location of the files. To actually get them to the new location you need to take the database off line in some way so you can move the physical files.

Either stop SQL, or take the DB off line, then copy/move the files and start SQL or bring the DB back on line.

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Perry Whittle SSC Guru Points: 233821 More actions · Answer 2

Stopping sql server is a bit like using a sledge hammer to crack a nut. You only need to take the database offline as follows

ALTER DATABASE mydb SET OFFLINE

Once offline, copy the disk files to the new locations.
Bring the database online.
When the database comes online successfully delete the old files.

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

"Ya can't make an omelette without breaking just a few eggs" 😉

sqlfriend SSC Guru Points: 52769 More actions · Answer 3

sqlfriend

SSC Guru

Points: 52769

December 5, 2012 at 9:44 am

#1565829

Thanks much, will give it a try

sqlfriend SSC Guru Points: 52769 More actions · Answer 4

By the way, what is the difference by take offline, then copy to anther directory , then bring online

with detach the database, copy to another directory, then attach it.

Which is better way?

Thanks

Perry Whittle SSC Guru Points: 233821 More actions · Answer 5

they both achieve the same result. Detaching removes the database from the system catalogs whereas offline doesn't.

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

"Ya can't make an omelette without breaking just a few eggs" 😉

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

Alter database is the preferred method of doing this. Its safer as the database is never actually removed form the instance so any risk when reattaching is mitigated. Also when you reattach the owner of the database could change, which may cause you an issue.

Whatever way you do it (and it can be done via backup\restore) having a backup to go back to is sensible.

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

sqlfriend SSC Guru Points: 52769 More actions · Answer 7

Thanks, but it seems detach and attach is easier and involves less steps to do.

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

Not enough to worry about! the alter database command was designed for just such tasks and is the safest way.

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

Perry Whittle SSC Guru Points: 233821 More actions · Answer 9

sqlfriends (12/5/2012)
Thanks, but it seems detach and attach is easier and involves less steps to do.

Do whatever you feel comfortable with, just know that in SQL server 2005 on ALTER DATABASE is the preferred method

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

"Ya can't make an omelette without breaking just a few eggs" 😉

Leo.Miller SSChampion Points: 13046 More actions · Answer 10

I strongly recommend you take the database OFFLINE rather than detaching. Detaching as mentioned removes the database from the system catalog, which means some database options (saved in the system catalog) are lost. These don't come back when you attache the database. An example of this id database chaining (which I recommend you don't used if at all possible).

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Leo.Miller SSChampion Points: 13046 More actions · Answer 11

Perry Whittle (12/5/2012)
Stopping sql server is a bit like using a sledge hammer to crack a nut. You only need to take the database offline as follows
ALTER DATABASE mydb SET OFFLINE
Once offline, copy the disk files to the new locations.
Bring the database online.
When the database comes online successfully delete the old files.

Yes I know 🙂 But I've known DBAs who want to do it that way.

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Hemant.R Ten Centuries Points: 1286 More actions · Answer 12

before moving file please take full backup of that database .

if you are using attached detached for this there may problem .

i had face problem while moving file to another location. it is giving file copying error,insufficient system resource ,

new file location is SAN drive and

mdf size is around 58 GB

or use attached detached,

please make database offline then move.

sqlfriend SSC Guru Points: 52769 More actions · Answer 13

Perry Whittle (12/5/2012)
Stopping sql server is a bit like using a sledge hammer to crack a nut. You only need to take the database offline as follows
ALTER DATABASE mydb SET OFFLINE
Once offline, copy the disk files to the new locations.
Bring the database online.
When the database comes online successfully delete the old files.

It looks a step missing in above.

Before take offline, should there be a step that change the logical file to another directory?

I know how to do in SQL for this step, is there a way to do it in ssms?

Thanks

Perry Whittle SSC Guru Points: 233821 More actions · Answer 14

sqlfriends (12/10/2012)
Perry Whittle (12/5/2012)
Stopping sql server is a bit like using a sledge hammer to crack a nut. You only need to take the database offline as follows
ALTER DATABASE mydb SET OFFLINE
Once offline, copy the disk files to the new locations.
Bring the database online.
When the database comes online successfully delete the old files.
It looks a step missing in above.
Before take offline, should there be a step that change the logical file to another directory?
I know how to do in SQL for this step, is there a way to do it in ssms?
Thanks

Yes, you need to use the ALTER DATABASE ... MODIFY FILE command but in your first post you've already done that!!!

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

"Ya can't make an omelette without breaking just a few eggs" 😉