DB--Move database files to another Drive

--Move database files to another Drive
--https://www.sqlservercentral.com/scripts/move-database-files-to-another-drive
We had a couple of times the demand to transfer SQL related files from C:\ drive to D:\ drive because system backups need them to be elsewhere and the person who installed SQL Server (and the databases) was not DBA.

Here is the way I recommand to run the script if you are not used to use it :

Copy-Paste the code of the script in a connection to the SQL Server instance of your choice
Modify the #CHANGEME to 1 so that the script does not change anything on the server
Execute the script
Copy the result in another window and run the commands
We ran it a couple of times and it seems to be working as expected.

-- https://msdn.microsoft.com/en-us/library/ms345483.aspx
-- https://msdn.microsoft.com/fr-be/library/ms345408%28v=sql.105%29.aspx
-- OK for SQL Server 2008 R2 Express Edition
/* 
!!! REPLACE #CHANGEME by 1 if just to test and by 0 if execution !!!
*/

-- ---------------------------------------------------------------------------------------------------
PRINT 'Setting user databases offline'
-- ---------------------------------------------------------------------------------------------------

DECLARE @JustTest BIT = #CHANGEME
DECLARE GetDDL CURSOR FOR 
    select 'ALTER DATABASE ' + QUOTENAME(name) + ' SET OFFLINE' from master.sys.databases
    where name not in ('master','tempdb','model','msdb') -- system databases cannot be set offline !

DECLARE @tsql NVARCHAR(MAX);    
OPEN GetDDL ;

fetch next from GetDDL into @tsql;

WHILE @@FETCH_STATUS = 0
BEGIN 
    if(@JustTest = 1) 
    BEGIN
        PRINT @tsql ;
    END 
    ELSE 
    BEGIN 
        exec sp_executesql @tsql ;
    END 
    fetch next from GetDDL into @tsql;
END 

CLOSE GetDDL
DEALLOCATE GetDDL
GO 

    
PRINT 'Move data and log files to D:\ drive'

DECLARE @JustTest BIT = #CHANGEME
DECLARE GetDDL CURSOR FOR 
    select 
        'ALTER DATABASE ' 
            + QUOTENAME(DB_NAME(database_id)) +	
        ' modify FILE ( NAME = ' + name + ', FILENAME=''' + REPLACE(physical_name,'C:\','D:\') + ''')' as DDLs
    from master.sys.master_files
    where DB_NAME(database_id) <> 'master' ; -- master needs a setting at service startup level
    
DECLARE @tsql NVARCHAR(MAX);    
OPEN GetDDL ;

fetch next from GetDDL into @tsql;

WHILE @@FETCH_STATUS = 0
BEGIN 
    if(@JustTest = 1) 
    BEGIN
        PRINT @tsql ;
    END 
    ELSE 
    BEGIN 
        exec sp_executesql @tsql ;
    END 
	fetch next from GetDDL into @tsql;
END 

CLOSE GetDDL
DEALLOCATE GetDDL
GO     


-- ---------------------------------------------------------------------------------------------------
PRINT 'YOU MUST COPY FILES TO NEW LOCATION then execute the following commands'
PRINT '!!! If an error occurs when restarting => adjust file permission to allow SQL Service account to access those files'
-- ---------------------------------------------------------------------------------------------------

PRINT 'Bringing user databases ONLINE'

DECLARE @JustTest BIT = #CHANGEME
DECLARE GetDDL CURSOR FOR 
    select 'ALTER DATABASE ' + QUOTENAME(name) + ' SET ONLINE' from master.sys.databases
    where name <> 'master'

DECLARE @tsql NVARCHAR(MAX);    
OPEN GetDDL ;

fetch next from GetDDL into @tsql;

WHILE @@FETCH_STATUS = 0
BEGIN 
    if(@JustTest = 1) 
    BEGIN
        PRINT @tsql ;
    END 
    ELSE 
    BEGIN 
        exec sp_executesql @tsql ;
    END
    fetch next from GetDDL into @tsql;
END 

CLOSE GetDDL
DEALLOCATE GetDDL
GO 

if(exists(select * from master.sys.master_files where DB_NAME(database_id) = 'master' and SUBSTRING(physical_name,0,4) = 'C:\'))
BEGIN 
    PRINT '!!!!! MASTER Database needs to be moved too !!!!'
    PRINT 'Here is the procedure : '
    PRINT ''
    PRINT '1) Open SQL Server Configuration Manager'
    PRINT '2) On "SQL Server Services" tab, right-click on the service related to the instance you need to take care of'
    PRINT '3) Choose "Properties" in the pop-up menu'
    PRINT '4) Go to advanced settings and edit startup parameters'
    PRINT '5) Edit at least "-d"(master.mdf) and "-l" (master.ldf) parameters. Optionnally also edit "-e" (errorlog) option'
    PRINT '   Example : -dD:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-eD:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lD:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf'
    PRINT '6) Stop SQL Server services'
    PRINT '7) Move master.mdf and master.ldf to new location'
    PRINT '8) Restart database engine'
    PRINT '9) Check all is OK'
END 

PRINT 'Just to be sure : Restart SQL Server instance (and dependant services)'