Script creating the T-SQL script for detaching and reattaching the databases after path mangling

detach_and_reattach_after_path_mangling.sql

DECLARE @dest_path_file NVARCHAR(MAX)
DECLARE @dest_path_log NVARCHAR(MAX)

SET @dest_path_file = 'C:\pippo\pluto';
SET @dest_path_log = 'C:\pippo\plog';

DECLARE cdb CURSOR FOR 
	SELECT name, database_id FROM sys.databases WHERE database_id > 4;

OPEN cdb;

DECLARE @name NVARCHAR(255);
DECLARE @dbid INT;
DECLARE @file NVARCHAR(MAX);
DECLARE @file_path NVARCHAR(MAX); 
DECLARE @type_desc NVARCHAR(255);

DECLARE @stmt NVARCHAR(MAX) = '';
DECLARE @detach_stmt NVARCHAR(MAX) = '';
DECLARE @log_stmt NVARCHAR(MAX);

DECLARE @file_first BIT;
DECLARE @log_first BIT;

FETCH NEXT FROM cdb INTO @name, @dbid

WHILE @@FETCH_STATUS = 0 
BEGIN	
	DECLARE @ls NVARCHAR(MAX) = ' Processing ' + @name + ' ';
	SET @stmt += REPLICATE('-', 64) + CHAR(13)+CHAR(10);
	SET @stmt += REPLICATE('-', 32-LEN(@ls)/2-1) + @ls + REPLICATE('-', 32-LEN(@ls)/2-1) + CHAR(13)+CHAR(10);
	SET @stmt += REPLICATE('-', 64) + CHAR(13)+CHAR(10);

	SET @stmt += 'PRINT(''Processing ' + @name + '...'');' + CHAR(13)+CHAR(10) + 'GO' + CHAR(13)+CHAR(10);

	SET @detach_stmt += 'ALTER DATABASE ' + QUOTENAME(@name) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;' + CHAR(13)+CHAR(10) + 'GO' + CHAR(13)+CHAR(10);
	SET @detach_stmt += 'sp_detach_db(' + @name + ');' + CHAR(13)+CHAR(10) + 'GO' + CHAR(13)+CHAR(10);

	SET @stmt += 'CREATE DATABASE ' + QUOTENAME(@name) + CHAR(13)+CHAR(10) + '     ON ';
	SET @log_stmt = CHAR(13)+CHAR(10) + '     LOG ON ';

	SET @file_first = 1;
	SET @log_first = 1;

	DECLARE crow CURSOR FOR SELECT name, physical_name, type_desc FROM sys.master_files WHERE database_id = @dbid;
	OPEN crow;
	FETCH NEXT FROM crow INTO @file, @file_path, @type_desc
	WHILE @@FETCH_STATUS = 0 	
	BEGIN
		IF @type_desc = 'LOG'
			BEGIN
				IF @log_first = 1 BEGIN SET @log_first = 0; END ELSE BEGIN SET @log_stmt += ', '; END;
				SET @log_stmt += CHAR(13)+CHAR(10) + '       (NAME=''' + @file + ''', FILENAME=''' + @dest_path_log + SUBSTRING(@file_path, LEN(@file_path)-CHARINDEX('\', REVERSE(@file_path))+1, 10000) + ''') ';
			END
		ELSE 
			BEGIN
				IF @file_first = 1 BEGIN SET @file_first = 0; END ELSE BEGIN SET @stmt += ', '; END;
				SET @stmt += CHAR(13)+CHAR(10) +  '       (NAME=''' + @file + ''', FILENAME=''' + @dest_path_file + SUBSTRING(@file_path, LEN(@file_path)-CHARINDEX('\', REVERSE(@file_path))+1, 10000) + ''') ';
			END
		FETCH NEXT FROM crow INTO @file, @file_path, @type_desc
	END
	CLOSE crow;
	DEALLOCATE crow;

	--SELECT * FROM sys.master_files 
	--WHERE database_id = @dbid

	FETCH NEXT FROM cdb INTO @name, @dbid

	SET @stmt += @log_stmt + CHAR(13)+CHAR(10) + 'FOR ATTACH;' + CHAR(13)+CHAR(10) + 'GO' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10);
END

PRINT CAST(@detach_stmt AS NTEXT)

PRINT '----------------------------------------------------------------';
PRINT '-------------------------Cut here-------------------------------';
PRINT '-------------------------Cut here-------------------------------';
PRINT '-------------------------Cut here-------------------------------';
PRINT '----------------------------------------------------------------'+ CHAR(13)+CHAR(10)+ CHAR(13)+CHAR(10)+ CHAR(13)+CHAR(10)+ CHAR(13)+CHAR(10);

PRINT CAST(@stmt AS NTEXT)

CLOSE cdb;
DEALLOCATE cdb;