SQL Server – Moving Partitioning Files to Another Disk Drive

migrationsql serversql-server-2008sql-server-2008-r2sql-server-2012

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?

Best Answer

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'
);
Related Question