0

There is a production table which was partitioned by someone in our company. Some partitioning files was wrongly located to different disk drive.

I have to move those files to the disk where they have to be. Those partitioned files for 2020 and 2021 are on production. How can I move those files to the main disk drive without stopping the database or services?

  • The files should be empty so just drop them and add the new ones to the place you want. – Denis Rubashkin Nov 4 '19 at 9:06
  • As far as i know, there was some data on those files. Is there a way to save the data before dropping them? @DenisRubashkin – Dbo Nov 4 '19 at 9:09
  • 1
    If there is some data inside a database file, that file can't be dropped. If you tried you would get an error ( I wouldn't try to check it out on the production though ). Anyway, you can add a new file to the filegroup and then use a command DBCC SHRINKFILE (N'OldFileLogicalName' , EMPTYFILE) to move data from the old file. – Denis Rubashkin Nov 4 '19 at 9:25
  • @DenisRubashkin Thank you very much for the help and informations you shared with me. Will this command " DBCC SHRINKFILE (N'OldFileLogicalName' , EMPTYFILE) " move the data new logical file? If so, where should i define the destination logical filename in the command? Thank you. – Dbo Nov 4 '19 at 9:50
0

To move a data file, if you don't have a maintain window, you should perform several steps:

Add a new file to the filegroup (Add Data or Log Files to a Database):

ALTER DATABASE [YourDatabaseName]
ADD FILE ( 
      NAME = N'NewFileLogicalName'
    , FILENAME = N'C:\Data\NewFileName.ndf'
    , SIZE = 8MB
    , FILEGROWTH = 128MB 
) TO FILEGROUP [YourFilegroupName];

Empty the old file (DBCC SHRINKFILE):

DBCC SHRINKFILE (N'OldFileLogicalName' , EMPTYFILE);

Remove the old file (Delete Data or Log Files from a Database):

ALTER DATABASE [YourDatabaseName]
REMOVE FILE OldFileLogicalName;

If a logical name matters (ALTER DATABASE File and Filegroup Options):

ALTER DATABASE [YourDatabaseName]
MODIFY FILE (
    NAME = N'NewFileLogicalName'
    , NEWNAME = N'OldFileLogicalName'
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.