MS SQL Server: Easy to Move DB Files

Product: MS SQL Server
Version: 2000 - 2017

Used following steps to move MS SQL Server data files, except master database

1. Generate SQL to specify the new file location, where new file location is D:\SQLServer2017\Data

SELECT name, physical_name , state_desc  , 'ALTER DATABASE ' + db_name(database_id) + ' MODIFY FILE ( NAME=' + name + ', FILENAME = ''D:\SQLServer2017\Data\'+
   substring(physical_name, 72,30)  + ''')' Generated_Stmt
FROM sys.master_files 
where database_id <> DB_ID('master')

2. Run the SQL generated from column "Generated_Stmt" such as

ALTER DATABASE tempdb MODIFY FILE ( NAME=tempdev, FILENAME = 'D:\SQLServer2017\Data\')
ALTER DATABASE tempdb MODIFY FILE ( NAME=templog, FILENAME = 'D:\SQLServer2017\Data\')
ALTER DATABASE model MODIFY FILE ( NAME=modeldev, FILENAME = 'D:\SQLServer2017\Data\')
ALTER DATABASE model MODIFY FILE ( NAME=modellog, FILENAME = 'D:\SQLServer2017\Data\')
ALTER DATABASE msdb MODIFY FILE ( NAME=MSDBData, FILENAME = 'D:\SQLServer2017\Data\')
ALTER DATABASE msdb MODIFY FILE ( NAME=MSDBLog, FILENAME = 'D:\SQLServer2017\Data\')

3. Restart MS SQL Server daemon from Windows Services