MSSQL Script: Alle LOG Files in einer DB verschieben

-- First, create a temporary table to store the file move operations
CREATE TABLE #LogFileChanges (
    DatabaseName sysname,
    LogicalFileName sysname,
    CurrentPath nvarchar(1000),
    NewPath nvarchar(1000)
);

-- Populate the temporary table with all log files and their new locations
INSERT INTO #LogFileChanges (DatabaseName, LogicalFileName, CurrentPath, NewPath)
SELECT 
    db.name AS DatabaseName,
    mf.name AS LogicalFileName,
    mf.physical_name AS CurrentPath,
    -- Construct new path - replace with your desired path
    REPLACE(mf.physical_name, 
        LEFT(mf.physical_name, LEN(mf.physical_name) - CHARINDEX('\', REVERSE(mf.physical_name)) + 1),
        'L:\SQL\LOG\') AS NewPath
FROM sys.master_files mf
INNER JOIN sys.databases db ON db.database_id = mf.database_id
WHERE mf.type_desc = 'LOG'
AND db.state_desc = 'ONLINE'
AND db.name NOT IN ('master', 'tempdb','NAV-Entwicklung','NAV-Release')
AND  mf.physical_name not like 'L%';

-- Exclude system databases that shouldn't be moved

-- Generate and execute ALTER DATABASE statements for each log file
DECLARE @sql nvarchar(max);
DECLARE @dbName sysname;
DECLARE @logicalName sysname;
DECLARE @currentPath nvarchar(1000);
DECLARE @newPath nvarchar(1000);
DECLARE @errorMessage nvarchar(1000);

-- Cursor to process each log file
DECLARE log_cursor CURSOR FOR 
SELECT DatabaseName, LogicalFileName, CurrentPath, NewPath 
FROM #LogFileChanges;

OPEN log_cursor;
FETCH NEXT FROM log_cursor INTO @dbName, @logicalName, @currentPath, @newPath;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        -- Create the ALTER DATABASE statement
        SET @sql = 'ALTER DATABASE [' + @dbName + '] SET OFFLINE WITH ROLLBACK IMMEDIATE;' + CHAR(13) +
                   'ALTER DATABASE [' + @dbName + '] MODIFY FILE ( NAME = N''' + @logicalName + ''', FILENAME = N''' + @newPath + ''' );' + CHAR(13) 
                   

        -- Execute the statement
        PRINT 'Moving log file for database: ' + @dbName;
        print @sql;
                               --EXEC sp_executesql @sql;

        -- After successful execution, physically move the file
        DECLARE @xpcmdshell nvarchar(1000);
        SET @xpcmdshell = 'EXEC master.dbo.xp_cmdshell ''MOVE "' + @currentPath + '" "' + @newPath + '"''';
        
                               
        EXEC sp_executesql @xpcmdshell;
        SET @sql ='ALTER DATABASE [' + @dbName + '] SET ONLINE;';
        EXEC sp_executesql @sql;
        PRINT 'Successfully moved log file for database: ' + @dbName;
    END TRY
    BEGIN CATCH
        SET @errorMessage = 'Error moving log file for database ' + @dbName + ': ' + ERROR_MESSAGE();
        PRINT @errorMessage;
        
        -- Attempt to bring database back online if it failed
        SET @sql = 'IF EXISTS (SELECT 1 FROM sys.databases WHERE name = ''' + @dbName + ''' AND state_desc = ''OFFLINE'')
                    BEGIN
                        ALTER DATABASE [' + @dbName + '] SET ONLINE;
                    END';
        print @sql
                --EXEC sp_executesql @sql;
    END CATCH

    FETCH NEXT FROM log_cursor INTO @dbName, @logicalName, @currentPath, @newPath;
END

-- Clean up
CLOSE log_cursor;
DEALLOCATE log_cursor;
DROP TABLE #LogFileChanges;

-- Print completion message
PRINT 'Log file move operation completed. Please verify all databases are online and accessible.';

-- Verify database states
SELECT name, state_desc 
FROM sys.databases 
WHERE name NOT IN ('master', 'tempdb')
ORDER BY name;