Moving User Databases from the C: drive en mass

I’ve been involved in helping some customers move databases (user and system) from the C: drive on a few projects now. This is particular an issue when the C: is the default or when Vendors are left to install databases on a server by themselves – quite a few don’t realize where the data and log files go, even if the volumes are called “SQL Data” and “SQL Logs”.

Here is a script I have been using. Apologies that its not very polished, but it has worked well for me. It can be used to move 1 or many databases and can either do the move or just generate the script to do the move so you can do it manually. It can also handle databases with multiple files and logical names which are not standard – a few other sample scripts I found fell just fell short on these two points.

/* Sample Script to move one or ALL user Databases to another location    
*/

DECLARE @single_database sysname    --name of single database if only one
DECLARE @logical_name VARCHAR(50)        -- database name      
DECLARE @path sysname            -- path for data files
DECLARE @log_path sysname        -- path for Log Files
DECLARE @dbid bigint            -- db id   
DECLARE @db_name sysname
DECLARE @physical_name nvarchar(4000)
DECLARE @type_desc sysname
DECLARE @enableCMDPermanently bit 
DECLARE @file_name sysname
DECLARE @cmd nvarchar(4000)         
DECLARE @cmdExec nvarchar(4000)           
DECLARE @NoExecute bit            --Set this variable to not execute


-- ============USER CONFIGURABLE VARIABLES START=============   
SET @path = 'F:\Data\' --'S:\Data\'   -- Destination Path for all Databases   
SET @log_path = 'G:\Logs\'
SET @single_database    ='MyDatabase'    --empty string means ALL databases on C: drive to be moved
SET @enableCMDPermanently = 1   
SET @NoExecute =1
-- ============USER CONFIGURABLE VARIABLES FINISH============   
  
-- =====Please do not edit variables below this line=========   
SET NOCOUNT ON   
IF NOT EXISTS (select * from sys.configurations where name='xp_cmdshell' and value=1)
BEGIN
    EXEC master.dbo.sp_configure 'show advanced options', 1   
    RECONFIGURE   
    EXEC master.dbo.sp_configure 'xp_cmdshell', 1 -- enable CMD   
    RECONFIGURE   WITH OVERRIDE
END

-- Table variable for db file details   
DECLARE @sysfiles TABLE   
(   
    name nvarchar(max),   
    physical_name nvarchar(max),   
    database_id bigint,   
    type_desc nvarchar(max)   
)   
  
INSERT INTO @sysfiles (name,physical_name,database_id,type_desc) 
SELECT [name], [physical_name],[database_id],type_desc 
FROM sys.master_files   
where 
db_name (database_id) NOT IN ('master','model','msdb','tempdb')   
AND DATABASEPROPERTYEX(db_name (database_id), 'Status')='ONLINE'
and type_desc<> 'FULLTEXT'
AND (db_name  (database_id) =@single_database 
OR (physical_name like 'C:%' and @single_database='') )  
-- Start CURSOR to iterate through database ids   
DECLARE db_cursor CURSOR FOR 
SELECT [database_id], db_name(database_id) FROM @sysfiles GROUP BY [database_id]  ORDER BY db_name(database_id) 
OPEN db_cursor      
FETCH NEXT FROM db_cursor INTO @dbid     , @db_name 
  
WHILE @@FETCH_STATUS = 0      
BEGIN   
  
    -- Force Disconnect Active Connections to Database   
    SET @cmd = 'ALTER DATABASE ' +  quotename(@db_name) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'   
    PRINT @cmd  
    IF @NoExecute=0 EXECUTE sp_executesql @cmd   
  
    SET @cmd = 'ALTER DATABASE ' +  quotename(@db_name) + ' SET OFFLINE WITH ROLLBACK IMMEDIATE'   
    PRINT @cmd      
    IF @NoExecute=0 EXECUTE sp_executesql @cmd   
  
     -- Move Files to a new location
    DECLARE file_cursor CURSOR FOR SELECT name,physical_name, type_desc
    FROM @sysfiles 
    WHERE database_id=@dbid
    
    OPEN file_cursor      
    FETCH NEXT FROM file_cursor INTO @logical_name, @physical_name, @type_desc
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @file_name=RIGHT(@physical_name, CHARINDEX('\', REVERSE(@physical_name))-1)
        
        --Move File and ALTER DATABASE DEFINTION
        IF @type_desc = 'ROWS'
        BEGIN
            set @cmdExec ='move ' + '"'+@physical_name+'"' + ' ' + '"'+@path + @file_name + '"'
            SET @cmd = 'ALTER DATABASE '+ quotename(@db_name) +' MODIFY FILE ( NAME = '+quotename(@logical_name)+', FILENAME = '''+@path + @file_name + ''' )'   
        END
        ELSE
            BEGIN
                set @cmdExec ='move ' + '"'+@physical_name+'"' + ' ' + '"'+@log_path + @file_name + '"'
                SET @cmd = 'ALTER DATABASE ['+ @db_name +'] MODIFY FILE ( NAME = '''+@logical_name+''', FILENAME = '''+ @log_path + @file_name + ''' )'   
            END

        -- xp_cmdshell command to move file
        SET @cmdExec = 'exec xp_cmdshell ''' + @cmdExec+ ''',no_output' 
        PRINT @cmdExec
        PRINT @cmd
           IF @NoExecute=0 EXECUTE sp_executesql @cmdExec

        --Execute ALTER DATABASE
        IF @NoExecute=0 EXECUTE sp_executesql @cmd 

        FETCH NEXT FROM file_cursor INTO @logical_name, @physical_name, @type_desc
    END
    CLOSE file_cursor
    DEALLOCATE file_cursor
    
    SET @cmd = 'ALTER DATABASE ' +  quotename(@db_name) + ' SET MULTI_USER WITH ROLLBACK IMMEDIATE'  
    PRINT @cmd
    IF @NoExecute=0 EXECUTE sp_executesql @cmd 

    SET @cmd = 'ALTER DATABASE ' +  quotename(@db_name) + ' SET ONLINE WITH ROLLBACK IMMEDIATE'   
    PRINT @cmd
    IF @NoExecute=0 EXECUTE sp_executesql @cmd 

    FETCH NEXT FROM db_cursor INTO @dbid  , @db_name    
  
 END      

CLOSE db_cursor      
DEALLOCATE db_cursor    

--OPTIONAL   
IF @enableCMDPermanently = 0   
BEGIN   
    EXEC master.dbo.sp_configure 'show advanced options', 1   
    RECONFIGURE   
    EXEC master.dbo.sp_configure 'xp_cmdshell', 0 --disable CMD   
    RECONFIGURE   
END -->