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