December 9, 2007 at 7:49 pm
Gud day we have a server (MSSQL 2000) with 13 databases and mdf and ldf's are located in Drive D. My superior wants those mdf and ldfs to be put in Drive F without down time. He said a long time ago that he was able to change tempdb mdf and ldf from drive D to another drive.
Does anyone know any script about this?? is this possible?? thanks in advance
"-=Still Learning=-"
Lester Policarpio
December 9, 2007 at 8:08 pm
with NO downtime isn't possible...you can minimize it to under a minute per database, assuming the databases are not huge terabyte sized db's. two ways i can think of, maybe more:
1: this is what i would do: back up the database, then restore by saying Add Device...file and find the backup you just did....from Enterprise Manager, when you go to restore it, you can got select the "options Tab" and change the path for the mdf files. SQL Server will create new mdf and ldf files on the new drive. downtime would be how ever many seconds it takes SQL Server to reserve the space on the new drive,a dn then copy the data from the bakup into the new mdfs.
2: you could detach a database from the server, MOVE the current mdf/ldf files to the new drive, and then reattach the mdf's. that would take as long as the file subsystem takes to copy x gigs of data from one drive to another.
Lowell
December 9, 2007 at 8:31 pm
Lowell has a good idea. You could do a backup /restore (with NORECOVERY), then copy logs, repeat until you're only 1 minute behind. Then backup the tail, restore, detach the first db, rename the second.
Doing this without downtime, including tempdb is impossible. Even tempdb requires downtime.
December 9, 2007 at 8:46 pm
Thanks for the quick answer guys. The first thing comes into my mind is dettaching the database then put the mdf and ldf to the other drive then attaching it again but when i heard the word "No downtime" hehehe the first thing that came into my mind is that "Ill just ask others if this is possible". But anyways great idea thanks... Ill just stick to dettaching the database if this is the way to do it...
"-=Still Learning=-"
Lester Policarpio
December 10, 2007 at 2:16 am
You could, of course, for each database, create one or more extra files for each file group on the new drive, then empty the existing file(s) by migrating the data to the new file(s). (DBCC Shrinkfile with the Emptyfile option). You can then remove the old file(s) using Alter Database. That, effectively, will "move" your MDFs/LDFs.
That said, it's a fair amount of work, especially for 13 different databases, and I'm not sure I'd want to do it on any databases of any significant size whilst users were adding data. In short, it can be done, but "can" and "should" are definitely two different animals.
Semper in excretia, suus solum profundum variat
December 10, 2007 at 7:29 am
Don't think the above will move the mdf. Will get the data and objects out, but system tables, metadata, etc. are stuck until you detach/attach.
December 10, 2007 at 7:33 am
Steve Jones - Editor (12/10/2007)
Don't think the above will move the mdf. Will get the data and objects out, but system tables, metadata, etc. are stuck until you detach/attach.
You could be right, 'cos I've never tried it in practice. However, I was going on what it says in Books Online, where it states:
"EMPTYFILE
Migrates all data from the specified file to other files in the same filegroup. Because the Database Engine no longer allows data to be placed in the empty file, the file can be removed by using the ALTER DATABASE statement."
Now, I have to say I've seen theory and practice diverging in Books Online (and, indeed, other MS products) before now, but the implication is that everything would be moved.
Semper in excretia, suus solum profundum variat
December 10, 2007 at 7:38 am
EMPTYFILE should work for NDFs, but not sure it works in MDFs. I thought that error'd out because you can't remove an MDF, nor create a 2nd.
Hmmm, guess I need to test this a bit more.
December 10, 2007 at 8:22 am
Well, the tests I've just carried out aren't too hopeful. In both cases, the dbcc shrinkfile bombs out with an error about being unable to move all the contents of the original file in order to complete the emptyfile operation. May well be that Steve's right. I'll keep digging, but let's assume for the moment the original answer to be correct, and my posting to be a red herring.
However, whilst trawling around in search of something more concrete, I found it's perfectly possible to use Alter Database to rename files for tempdb only, since the change actually occurs at the next reboot of the server, since tempdb is recreated at startup time. Therefore, in relation to Lester's boss' original assertion, it's likely he used the Alter Database command to change the files' paths, and believed the change to have occurred immediately. Perhaps, Lester, that'll give you the ammunition to show that what he achieved with Tempdb is not possible in the same way with any other database.
Semper in excretia, suus solum profundum variat
December 10, 2007 at 8:37 am
That appears to be the case here as well. I can use indexing to move the tables to new filegroups, but can't move metadata. Can't use DBCC to get things into a new file in the same filegroup because of the metadata as well (at least, that's what I believe.
You should be able to move the contents and majority of data over to the new drive, but you'd be stuck with the MDFs on that original drive. That would get you space back.
Also, if you decide to move the to new files/filegroups (2nd recommended), I'd suggest that you make the new one the default so you don't end up putting lots of stuff back in the original place.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply