Move database files to another Drive

-- 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)'