Move Databases to New Storage

Moving a server's databases to new storage? This script walks every online user database and prints the ALTER DATABASE ... MODIFY FILE statement needed to move each data file to @NewDatLocation and each log file to @NewLogLocation, keeping the original physical file names. Set either location to NULL to skip moving that file type. (Run the statements after taking the databases offline or one at a time.)

SET NOCOUNT ON;

DECLARE @NewDatLocation varchar(256) SET @NewDatLocation = 'U:\SQLData\' --Set to NULL to not move the data files.
DECLARE @NewLogLocation varchar(256) SET @NewLogLocation = 'L:\SQLData\' --Set to NULL to not move the log files.

DECLARE @tSQL VarChar(MAX)
DECLARE @DatabaseName sysname
DECLARE @FileName sysname
DECLARE @FileType sysname
DECLARE @PhysicalFile nVarChar(256)

CREATE TABLE #DatabaseFiles
(
	[FileName] sysname,
	[FileType] sysname,
	[PhysicalFile] nvarchar(256)
)

DECLARE DatabaseList CURSOR FAST_FORWARD FOR
	SELECT
		name
	FROM
		sys.databases
	WHERE
		state_desc = 'ONLINE'
		AND name NOT IN ('master','tempdb','model','msdb')
OPEN DatabaseList
FETCH FROM DatabaseList INTO @DatabaseName

WHILE(@@FETCH_STATUS = 0)
BEGIN--WHILE
	----------------------------------------------------------------------------------------------------------------------------------
	TRUNCATE TABLE #DatabaseFiles

	SET @tSQL =
		'INSERT INTO #DatabaseFiles' + CHAR(13) + CHAR(10) +
		'SELECT' + CHAR(13) + CHAR(10) +
			'name as [FileName],' + CHAR(13) + CHAR(10) +
			'type_desc AS [FileType],' + CHAR(13) + CHAR(10) +
			'SUBSTRING([physical_name], LEN([physical_name]) - CHARINDEX(''\'', Reverse([physical_name])) + 2, 256) AS [PhysicalFile]' + CHAR(13) + CHAR(10) +
		'FROM' + CHAR(13) + CHAR(10) +
			'[' + @DatabaseName + '].sys.database_files' + CHAR(13) + CHAR(10)
	EXEC(@tSQL)
	
	DECLARE DatabaseFiles CURSOR FAST_FORWARD FOR
		SELECT
			[FileName],
			[FileType],
			[PhysicalFile]
		FROM
			#DatabaseFiles
	OPEN DatabaseFiles
	FETCH FROM DatabaseFiles INTO @FileName, @FileType, @PhysicalFile
	
	--SET @tSQL = 
		
	WHILE(@@FETCH_STATUS = 0)
	BEGIN--WHILE
		----------------------------------------------------------------------------------------------------------------------------------

	
		IF(@FileType = 'ROWS')
		BEGIN--IF
			IF(IsNull(@NewDatLocation, '') <> '')
			BEGIN--IF
				PRINT 'ALTER DATABASE [' + @DatabaseName + '] MODIFY FILE ( NAME = [' + @FileName + '] , FILENAME = ''' + Replace(@NewDatLocation + '\\' + @PhysicalFile, '\\\\', '\\') + ''' )'
			END--IF
		END ELSE IF(@FileType = 'LOG') BEGIN--IF 
			IF(IsNull(@NewLogLocation, '') <> '')
			BEGIN--IF
				PRINT 'ALTER DATABASE [' + @DatabaseName + '] MODIFY FILE ( NAME = [' + @FileName + '] , FILENAME = ''' + Replace(@NewLogLocation + '\\' + @PhysicalFile, '\\\\', '\\') + ''' )'
			END--IF
		END--IF

		----------------------------------------------------------------------------------------------------------------------------------
		FETCH FROM DatabaseFiles INTO @FileName, @FileType, @PhysicalFile
	END--WHILE

	--PRINT @tSQL

	CLOSE DatabaseFiles DEALLOCATE DatabaseFiles
	
	----------------------------------------------------------------------------------------------------------------------------------
	FETCH FROM DatabaseList INTO @DatabaseName
END--WHILE

CLOSE DatabaseList DEALLOCATE DatabaseList

DROP TABLE #DatabaseFiles