Script to Rename Many .ndf Physical Files

/**********************************************************************

SCRIPT NAME: Rename FileNames 02.sql

PURPOSE: Rename multiple filenames on the database. 

Change History: 
 03/19/2010 4:15 PM - gmilner: Created. 

**********************************************************************/
SET NOCOUNT ON

DECLARE @DATABASE_NAME VARCHAR(64) 
SET @DATABASE_NAME = 'REC_DW_Automation' 

PRINT '----------------------------------------------------------------------------'
PRINT ' RUN THESE IN A SEPARATE SSMS WINDOW' 
PRINT '----------------------------------------------------------------------------'

-- create the ALTER DATABASE files to change the file names in the sys

/* 
 NOTE: "This will only alter SQL Server's internal definition of the filename, 
 it will not change the actual name of the file in the OS file system." 
*/ 
SELECT 
 'ALTER DATABASE '+ @DATABASE_NAME + CHAR(13) + 
 ' MODIFY FILE (NAME = ' + [name] + ', ' + CHAR(13) + ' FILENAME = ''' + 
 /* the line below strips the path only from the whole (old) filename
 so the new files are mapped to the same place as the old */ SUBSTRING(physical_name,1,PATINDEX('%'+REVERSE(SUBSTRING(REVERSE(physical_name),1,CHARINDEX('\',REVERSE(physical_name))-1))+'%' ,physical_name)-1) + 
 @DATABASE_NAME + '_' + 
 REPLACE([name],'FG_','') + '.ndf'');' /* NOTE: all filegroups start with 'FG' but files should not. */ + CHAR(13) /* put in a blank line between each command */FROM sys.master_files
WHERE database_id = DB_ID(@DATABASE_NAME)
    AND physical_name LIKE '%.ndf';
    

-- Now we need DOS BATCH commandS to rename the actual files.
-- We run them as Administrator on the box itself

PRINT '----------------------------------------------------------------------------'
PRINT ' NOW, DETACH THE DATABASE AND THEN ... ' 
PRINT ' RUN THESE IN A DOS BATCH AS ADMINISTRATOR' 
PRINT '----------------------------------------------------------------------------'


SELECT 'rename "' + physical_name + 
 '" "' + 
 @DATABASE_NAME + '_' + 
 REPLACE([name],'FG_','') + '.ndf";' /* NOTE: all filegroups start with 'FG' but files should not. */

FROM sys.master_files
WHERE database_id = DB_ID(@DATABASE_NAME)
    AND physical_name LIKE '%.ndf';


PRINT '----------------------------------------------------------------------------'
PRINT ' AFTER THE DOS BATCH IS RUN, REATTACH THE DATABASE' 
PRINT '----------------------------------------------------------------------------'


SET NOCOUNT OFF