SET NOCOUNT ON
CREATE TABLE #DataFiles ( DBName SYSNAME, FileType VARCHAR(255), DBLogicalFileName SYSNAME, CurrentFileName VARCHAR(255), CurrentFilePath VARCHAR(255), NewFileName VARCHAR(255), NewFilePath VARCHAR(255) )
DECLARE @CurrentFileNamePart VARCHAR(255), @NewFileNamePart VARCHAR(255), @CurrentFilePathPart VARCHAR(255), @NewFilePathPart VARCHAR(255), @DBName VARCHAR(2000), @Cmd VARCHAR(2000), @DetachCommand VARCHAR(1000), @AttachCommand VARCHAR(1000), @AttachCommand_Files VARCHAR(1000)
SELECT -- change these 2 pairs of variables to represent any change on file name or file path that is to be implemented @CurrentFileNamePart = NULL, @NewFileNamePart = NULL, @CurrentFilePathPart = 'D:\Log', @NewFilePathPart = 'D:\Logs', @DetachCommand = '', @AttachCommand = '', @AttachCommand_Files = ''
-- get the list of databases to process and their file names
SELECT @DBName = MIN(Name) FROM sys.databases (NOLOCK) WHERE Name NOT IN ('master', 'model', 'tempdb', 'northwind', 'pubs', 'msdb')
WHILE @DBName IS NOT NULL BEGIN SELECT @Cmd = '' SELECT @Cmd = 'USE ' + @DBName + ' SELECT ''' + @DBName +''' As DBName,' + 'Name AS DBLogicalFileName, ' + 'Type_Desc AS FileType, ' + 'REVERSE(SUBSTRING(REVERSE(Physical_Name), 1, CHARINDEX(''\'', REVERSE(Physical_Name)) - 1)) AS CurrentFileNamePart, ' + 'SUBSTRING(Physical_Name, 1, CHARINDEX(REVERSE(SUBSTRING(REVERSE(Physical_Name), 1, CHARINDEX(''\'', REVERSE(Physical_Name)) - 1)), Physical_Name)-1) AS CurrentFilePathPart ' + 'FROM sys.database_files'
INSERT #DataFiles ( DBName, DBLogicalFileName, FileType, CurrentFileName, CurrentFilePath ) EXEC (@Cmd) SELECT @DBName = MIN(Name) FROM sys.databases (NOLOCK) WHERE Name NOT IN ('master', 'model', 'tempdb', 'northwind', 'pubs', 'msdb') AND Name > @DBName END
-- now perform the remapping UPDATE #DataFiles SET NewFileName = CASE WHEN @NewFileNamePart IS NULL THEN CurrentFileName ELSE REPLACE(CurrentFileName, @CurrentFileNamePart, @NewFileNamePart) END,
NewFilePath = CASE WHEN @NewFilePathPart IS NULL THEN CurrentFilePath ELSE REPLACE(CurrentFilePath, @CurrentFilePathPart, @NewFilePathPart) END
-- now, finally , we can create the detach and attach commands -- to move only the databases which have new file names or file paths SELECT @DBName = MIN(DBName) FROM #DataFiles (NOLOCK) WHERE ((NewFileName <> CurrentFileName) OR (NewFilePath <> CurrentFilePath))
WHILE @DBName IS NOT NULL BEGIN SELECT @DetachCommand = '', @AttachCommand = '', @AttachCommand_Files = ''
SELECT @DetachCommand = @DetachCommand + 'master.dbo.sp_detach_db @dbname = N''' + @DBName + ''' '
SELECT @AttachCommand = @AttachCommand + 'USE master ' + 'CREATE DATABASE [' + @DBName + '] ON' SELECT @AttachCommand_Files = @AttachCommand_Files + '( FILENAME = N''' + NewFilePath + NewFileName + ''' ),' FROM #DataFiles WHERE DBName = @DBName ORDER BY FileType DESC SELECT @AttachCommand_Files = SUBSTRING(@AttachCommand_Files, 1, DATALENGTH(@AttachCommand_Files) - 1)
-- I'm assuming that the db should be owned by sa. The attach will allocate the logged in user as owner -- so an additional command is required SELECT @AttachCommand = @AttachCommand + @AttachCommand_Files + 'FOR ATTACH ' + ' EXEC [' + @DBName + '].dbo.sp_changedbowner @loginame=N''sa'''
SELECT @DetachCommand SELECT @AttachCommand
SELECT @DBName = MIN(DBName) FROM #DataFiles (NOLOCK) WHERE DBName > @DBName AND ((NewFileName <> CurrentFileName) OR (NewFilePath <> CurrentFilePath))
END
DROP TABLE #DataFiles
SET NOCOUNT OFF
|