Below is the script we can use to move the data and log files for the databases except master.
DECLARE
@counter INT = 1,
@max INT = 0,
@NEW_DATA_PATH VARCHAR(500)='E:\XXXX\Data',
@NEW_LOG_PATH VARCHAR(500)='F:\XXXX\Log'
DECLARE @FILE_LIST TABLE (
[ID] int IDENTITY,
[DBNAME] VARCHAR(100),
[LNAME] VARCHAR(100),
[PNAME] VARCHAR(500),
[TYPE_DESC] VARCHAR(50)
)
INSERT INTO @FILE_LIST
SELECT n.NAME,f.NAME,f.PHYSICAL_NAME,f.TYPE_DESC
FROM MASTER.SYS.MASTER_FILES f,MASTER.SYS.DATABASES n WHERE n.NAME not in('MASTER')
and f.DATABASE_ID=n.DATABASE_ID
SELECT @max = COUNT([ID]) FROM @FILE_LIST
WHILE @counter <= @max
BEGIN
DECLARE @FILE_TYPE VARCHAR(100),
@DBNAME VARCHAR(100),
@LOGICAL_NAME VARCHAR(100),
@PHYSICAL_NAME VARCHAR(500) ;
SELECT @FILE_TYPE=TYPE_DESC,@DBNAME=[DBNAME],@LOGICAL_NAME=[LNAME],
@PHYSICAL_NAME=SUBSTRING([PNAME],LEN([PNAME])-CHARINDEX('\',REVERSE([PNAME]))+2,LEN([PNAME])-1)
FROM @FILE_LIST WHERE [ID] = @counter
IF(@FILE_TYPE='ROWS')
BEGIN
--ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );
--PRINT @FILE_TYPE+','+@DBNAME+','+@LOGICAL_NAME+','+@PHYSICAL_NAME
PRINT 'USE MASTER
GO
ALTER DATABASE '+@DBNAME+' MODIFY FILE ( NAME = '+@LOGICAL_NAME+', FILENAME = '''+@NEW_DATA_PATH+'\'+@PHYSICAL_NAME+''')
GO'
END
ELSE
PRINT 'USE MASTER
GO
ALTER DATABASE '+@DBNAME+' MODIFY FILE ( NAME = '+@LOGICAL_NAME+', FILENAME = '''+@NEW_LOG_PATH+'\'+@PHYSICAL_NAME+''')
GO'
SET @counter = @counter + 1
END
DECLARE
@counter INT = 1,
@max INT = 0,
@NEW_DATA_PATH VARCHAR(500)='E:\XXXX\Data',
@NEW_LOG_PATH VARCHAR(500)='F:\XXXX\Log'
DECLARE @FILE_LIST TABLE (
[ID] int IDENTITY,
[DBNAME] VARCHAR(100),
[LNAME] VARCHAR(100),
[PNAME] VARCHAR(500),
[TYPE_DESC] VARCHAR(50)
)
INSERT INTO @FILE_LIST
SELECT n.NAME,f.NAME,f.PHYSICAL_NAME,f.TYPE_DESC
FROM MASTER.SYS.MASTER_FILES f,MASTER.SYS.DATABASES n WHERE n.NAME not in('MASTER')
and f.DATABASE_ID=n.DATABASE_ID
SELECT @max = COUNT([ID]) FROM @FILE_LIST
WHILE @counter <= @max
BEGIN
DECLARE @FILE_TYPE VARCHAR(100),
@DBNAME VARCHAR(100),
@LOGICAL_NAME VARCHAR(100),
@PHYSICAL_NAME VARCHAR(500) ;
SELECT @FILE_TYPE=TYPE_DESC,@DBNAME=[DBNAME],@LOGICAL_NAME=[LNAME],
@PHYSICAL_NAME=SUBSTRING([PNAME],LEN([PNAME])-CHARINDEX('\',REVERSE([PNAME]))+2,LEN([PNAME])-1)
FROM @FILE_LIST WHERE [ID] = @counter
IF(@FILE_TYPE='ROWS')
BEGIN
--ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );
--PRINT @FILE_TYPE+','+@DBNAME+','+@LOGICAL_NAME+','+@PHYSICAL_NAME
PRINT 'USE MASTER
GO
ALTER DATABASE '+@DBNAME+' MODIFY FILE ( NAME = '+@LOGICAL_NAME+', FILENAME = '''+@NEW_DATA_PATH+'\'+@PHYSICAL_NAME+''')
GO'
END
ELSE
PRINT 'USE MASTER
GO
ALTER DATABASE '+@DBNAME+' MODIFY FILE ( NAME = '+@LOGICAL_NAME+', FILENAME = '''+@NEW_LOG_PATH+'\'+@PHYSICAL_NAME+''')
GO'
SET @counter = @counter + 1
END