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 :

1. Copy-Paste the code of the script in a connection to the SQL Server instance of your choice
2. Modify the #CHANGEME to 1 so that the script does not change anything on the server
3. Execute the script
4. 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.
Hope this helps.
 

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