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