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