Alle Dateien im Verzeichnis Restoren - AutoRestoreBackups-Prozedur

AutoRestoreBackups-Prozedur 

Die Prozedur liest alle Datenbank Backups eines Verzeichnisses und liest dies dann in den SQL-Server ein:

Parameter

Ausgabe

• Im @Whatif = 1-Modus werden die generierten RESTORE-Befehle auf der Konsole ausgegeben.

• Im Ausführungsmodus werden Erfolgs‑/Fehlermeldungen pro Datenbank ausgegeben.

/*
=====================================================================
Autor:       Janke
Datum:     2025-11  
Beschreibung: 
    Stellt alle Datenbank-Backups (.bak, .sav) aus einem Quellverzeichnis
    automatisch wieder her. Für jede Backup-Datei wird die enthaltene
    Datenbank ermittelt und ein RESTORE Befehl mit MOVE-Klauseln generiert.
    Die Zielverzeichnisse für Daten- und Log-Dateien können einzeln
    angegeben werden. Optional kann nur ein Whatif-Modus durchgeführt,
    eine Datenbank vorher in den SINGLE_USER-Modus versetzt und nachher
    zurückgesetzt werden, sowie eine Verifikation des Backups durchgeführt
    werden.

Parameter:
    @SourceDirBackupFiles  - Pfad, in dem die .bak/.sav Dateien liegen
    @Whatif                - 1 = Nur Befehle anzeigen, 0 = Ausführen
    @Single                - 1 = Vor Restore SINGLE_USER setzen (und zurück)
    @DestDirDbFiles        - Zielpfad für Data-Dateien (NULL = Standarddatenpfad)
    @DestDirLogFiles       - Zielpfad für Log-Dateien  (NULL = Standardlogpfad)
    @VerifyOnly            - 1 = Vor Restore VERIFYONLY ausführen
    @ReplaceExisting       - 1 = WITH REPLACE verwenden, 0 = Fehler bei existierender DB
    @Stats                 - Statistikanzeige (z.B. 10 = STATS = 10)
    @RecoveryState         - 'RECOVERY' oder 'NORECOVERY' (Standard = RECOVERY)

Abhängigkeiten:
    - SQL Server ab 2012 (SERVERPROPERTY mit 'InstanceDefaultDataPath' ab 2012? Tatsächlich ab 2012 verfügbar)
    - xp_dirtree (erfordert sysadmin, alternativ könnte man ein Directory-Listing per xp_cmdshell oder CLR verwenden)
=====================================================================
*/

CREATE OR ALTER PROCEDURE dbo.AutoRestoreBackups
    @SourceDirBackupFiles NVARCHAR(255) = 'F:\Daten\SQL\Backup\Usr-DB\',
    @Whatif BIT = 0,                         -- 0 = ausführen, 1 = nur anzeigen
    @Single BIT = 0,                         -- 1 = SINGLE_USER vorher/nachher
    @DestDirDbFiles NVARCHAR(255) = NULL,    -- NULL = Standarddatenpfad
    @DestDirLogFiles NVARCHAR(255) = NULL,   -- NULL = Standardlogpfad
    @VerifyOnly BIT = 0,                     -- 1 = RESTORE VERIFYONLY vorher
    @ReplaceExisting BIT = 1,                -- 1 = WITH REPLACE
    @Stats INT = 10,                         -- STATS = n, 0 = keine Ausgabe
    @RecoveryState NVARCHAR(10) = 'RECOVERY'  -- 'RECOVERY' oder 'NORECOVERY'
AS
BEGIN
    SET NOCOUNT ON;

    -- Standardpfade ermitteln (falls nicht angegeben)
    IF @DestDirDbFiles IS NULL
        SET @DestDirDbFiles = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS NVARCHAR(255));
    IF @DestDirLogFiles IS NULL
        SET @DestDirLogFiles = CAST(SERVERPROPERTY('InstanceDefaultLogPath') AS NVARCHAR(255));

    -- Pfade mit abschließendem Backslash versehen
    IF RIGHT(@DestDirDbFiles, 1) <> '\' SET @DestDirDbFiles = @DestDirDbFiles + '\';
    IF RIGHT(@DestDirLogFiles, 1) <> '\' SET @DestDirLogFiles = @DestDirLogFiles + '\';
    IF RIGHT(@SourceDirBackupFiles, 1) <> '\' SET @SourceDirBackupFiles = @SourceDirBackupFiles + '\';

    -- Temporäre Tabellen für Dateilisten und Backup-Metadaten
    CREATE TABLE #BackupFiles (FileName NVARCHAR(255), Depth INT, IsFile INT);
    CREATE TABLE #HeaderResult (BackupName NVARCHAR(128), BackupDescription NVARCHAR(255), BackupType SMALLINT, ...); -- alle Spalten
    CREATE TABLE #FileList (LogicalName NVARCHAR(128), PhysicalName NVARCHAR(260), Type CHAR(1), ...); -- alle Spalten

    -- 1. Dateiliste aus Verzeichnis holen
    INSERT INTO #BackupFiles
    EXEC master.dbo.xp_dirtree @SourceDirBackupFiles, 1, 1;

    -- 2. Alle .bak und .sav Dateien ermitteln (ohne Unterordner)
    DECLARE @BackupFiles TABLE (FileName NVARCHAR(255), FullPath NVARCHAR(500));
    INSERT INTO @BackupFiles (FileName, FullPath)
    SELECT bf.FileName, @SourceDirBackupFiles + bf.FileName
    FROM #BackupFiles bf
    WHERE bf.IsFile = 1
      AND (bf.FileName LIKE '%.bak' OR bf.FileName LIKE '%.sav');

    IF NOT EXISTS (SELECT 1 FROM @BackupFiles)
    BEGIN
        PRINT 'Keine .bak- oder .sav-Dateien im Verzeichnis gefunden.';
        RETURN;
    END;

    -- 3. Für jede Datei: Datenbanknamen, Backup-Typ, Dateiliste ermitteln
    DECLARE @FullPath NVARCHAR(500), @DbName SYSNAME, @BackupType SMALLINT;
    DECLARE @RestoreCmd NVARCHAR(MAX), @MoveCmd NVARCHAR(MAX);
    DECLARE @Index INT = 1, @MaxIndex INT;
    DECLARE @crlf CHAR(2) = CHAR(13) + CHAR(10);

    SELECT @MaxIndex = COUNT(*) FROM @BackupFiles;
    
    -- Temporäre Tabelle für Restore-Befehle (optional zum Protokollieren)
    CREATE TABLE #RestoreCommands (Seq INT, Cmd NVARCHAR(MAX));

    WHILE @Index <= @MaxIndex
    BEGIN
        SELECT @FullPath = FullPath, @FileName = FileName
        FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY FileName) AS rn FROM @BackupFiles) t
        WHERE rn = @Index;

        -- Header auslesen
        TRUNCATE TABLE #HeaderResult;
        BEGIN TRY
            INSERT INTO #HeaderResult
            EXEC ('RESTORE HEADERONLY FROM DISK = ''' + REPLACE(@FullPath, '''', '''''') + '''');
        END TRY
        BEGIN CATCH
            PRINT 'Fehler beim Lesen der Header von ' + @FullPath + ': ' + ERROR_MESSAGE();
            SET @Index = @Index + 1;
            CONTINUE;
        END CATCH

        -- Nur die erste Zeile verwenden (für einfache Backup-Dateien mit einem Set)
        SELECT TOP 1 @DbName = DatabaseName, @BackupType = BackupType
        FROM #HeaderResult;

        IF @DbName IS NULL
        BEGIN
            PRINT 'Keine gültige Datenbank in ' + @FullPath + ' gefunden.';
            SET @Index = @Index + 1;
            CONTINUE;
        END

        -- Prüfen, ob es sich um ein vollständiges Datenbank-Backup handelt (BackupType = 1)
        IF @BackupType <> 1
        BEGIN
            PRINT 'Überspringe ' + @FullPath + ' (BackupTyp = ' + CAST(@BackupType AS VARCHAR) + ' - nur vollständige DB-Backups werden unterstützt).';
            SET @Index = @Index + 1;
            CONTINUE;
        END

        -- Filelist auslesen
        TRUNCATE TABLE #FileList;
        BEGIN TRY
            INSERT INTO #FileList
            EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + REPLACE(@FullPath, '''', '''''') + '''');
        END TRY
        BEGIN CATCH
            PRINT 'Fehler beim Lesen der Filelist von ' + @FullPath + ': ' + ERROR_MESSAGE();
            SET @Index = @Index + 1;
            CONTINUE;
        END CATCH

        -- MOVE-Klauseln generieren
        SET @MoveCmd = '';
        SELECT @MoveCmd = @MoveCmd + 
            ', MOVE ' + QUOTENAME(LogicalName, '''') + ' TO ' + 
            QUOTENAME(
                CASE WHEN Type = 'D' THEN @DestDirDbFiles ELSE @DestDirLogFiles END + 
                REVERSE(SUBSTRING(REVERSE(PhysicalName), 1, CHARINDEX('\', REVERSE(PhysicalName)) - 1)),
                ''''
            ) + @crlf
        FROM #FileList;

        -- Basis-Restore-Befehl
        SET @RestoreCmd = 
            CASE WHEN @Single = 1 THEN 'ALTER DATABASE ' + QUOTENAME(@DbName) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;' + @crlf ELSE '' END +
            'RESTORE DATABASE ' + QUOTENAME(@DbName) + @crlf +
            'FROM DISK = ' + QUOTENAME(@FullPath, '''') + @crlf +
            'WITH ' + @crlf +
            CASE WHEN @ReplaceExisting = 1 THEN '  REPLACE,' + @crlf ELSE '' END +
            '  STATS = ' + CAST(@Stats AS VARCHAR) + ',' + @crlf +
            STUFF(@MoveCmd, 1, 2, '') +   -- erstes Komma/Leerzeichen entfernen
            '  ' + @RecoveryState + @crlf +
            CASE WHEN @Single = 1 THEN 'ALTER DATABASE ' + QUOTENAME(@DbName) + ' SET MULTI_USER;' + @crlf ELSE '' END;

        -- Optional: VERIFYONLY
        IF @VerifyOnly = 1
        BEGIN
            DECLARE @VerifyCmd NVARCHAR(MAX) = 'RESTORE VERIFYONLY FROM DISK = ' + QUOTENAME(@FullPath, '''') + ';';
            IF @Whatif = 1
                PRINT '-- Verifiziere: ' + @VerifyCmd;
            ELSE
            BEGIN
                BEGIN TRY
                    EXEC sp_executesql @VerifyCmd;
                END TRY
                BEGIN CATCH
                    PRINT 'VERIFYONLY fehlgeschlagen für ' + @FullPath + ': ' + ERROR_MESSAGE();
                    SET @Index = @Index + 1;
                    CONTINUE;
                END CATCH
            END
        END

        -- Befehl speichern/ausführen
        IF @Whatif = 1
            PRINT @RestoreCmd;
        ELSE
        BEGIN
            BEGIN TRY
                EXEC sp_executesql @RestoreCmd;
                PRINT 'Restore von ' + @DbName + ' erfolgreich.';
            END TRY
            BEGIN CATCH
                PRINT 'Fehler beim Restore von ' + @DbName + ': ' + ERROR_MESSAGE();
            END CATCH
        END

        SET @Index = @Index + 1;
    END

    -- Aufräumen
    DROP TABLE #BackupFiles;
    DROP TABLE #HeaderResult;
    DROP TABLE #FileList;
    DROP TABLE #RestoreCommands;
END;
GO

Einschränkungen

• Es werden nur vollständige Datenbank-Backups (BackupType = 1) unterstützt. Differential- oder Transaktionslog-Backups werden ignoriert.

• Eine Backup-Datei sollte nur ein Backup-Set enthalten. Bei mehreren Sets wird nur das erste verwendet.

• xp_dirtree erfordert die sysadmin-Rolle. Alternativ könnte man ein Directory Listing über xp_cmdshell oder eine CLR-Funktion realisieren.

• Das Skript ist nicht für Azure SQL Database geeignet (dort gibt es keine xp_dirtree).

-- Nur anzeigen, was passieren würde
EXEC dbo.AutoRestoreBackups 
    @SourceDirBackupFiles = 'D:\Backups\',
    @Whatif = 1,
    @DestDirDbFiles = 'E:\Data\',
    @DestDirLogFiles = 'F:\Logs\';

-- Tatsächlich wiederherstellen, bestehende DBs ersetzen, mit SINGLE_USER
EXEC dbo.AutoRestoreBackups 
    @SourceDirBackupFiles = 'D:\Backups\',
    @Whatif = 0,
    @Single = 1,
    @ReplaceExisting = 1;