DECLARE @Path NVARCHAR(255) = 'C:\BackupFolder\' -- Ścieżka do folderu z plikami .bak
DECLARE @File NVARCHAR(255)
DECLARE @RestoreCommand NVARCHAR(MAX)

CREATE TABLE #BackupFiles (FileName NVARCHAR(255))

-- Lista plików z folderu
INSERT INTO #BackupFiles
EXEC xp_cmdshell 'dir C:\BackupFolder\*.bak /b'

DELETE FROM #BackupFiles WHERE FileName IS NULL

DECLARE FileCursor CURSOR FOR
SELECT FileName FROM #BackupFiles

OPEN FileCursor
FETCH NEXT FROM FileCursor INTO @File

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @RestoreCommand = '
    RESTORE DATABASE [' + REPLACE(@File, '.bak', '') + '] 
    FROM DISK = ''' + @Path + @File + ''' 
    WITH MOVE ''' + REPLACE(@File, '.bak', '') + '_Data'' TO ''C:\SQLData\' + REPLACE(@File, '.bak', '') + '_Data.mdf'',
         MOVE ''' + REPLACE(@File, '.bak', '') + '_Log'' TO ''C:\SQLLog\' + REPLACE(@File, '.bak', '') + '_Log.ldf'',
         REPLACE'
         
    PRINT @RestoreCommand
    EXEC sp_executesql @RestoreCommand

    FETCH NEXT FROM FileCursor INTO @File
END

CLOSE FileCursor
DEALLOCATE FileCursor
DROP TABLE #BackupFiles
                                
                            

Paste Hosted With By Wklejamy.pl