Restore All DB Backups In a Folder

    No Comments

    It’s possible that you’d want to restore all the databases you have backed up to  a specific folder. This will generate the scripts to restore those and move the files to a new destination. Copy the output from this query into a new query window and modify as needed before running.

    Part of this code was taken from StackOverflow – I think this post in particular, but it’s hard to say for sure.

    SET NOCOUNT ON
    declare @folder as varchar(1000) = 'C:\Backups\ '
    declare @moveToDrive as varchar(20) = 'M'
    declare @allFiles as table(id int identity(1,1),file_nm varchar(200), depth_no int, is_file bit, fullPath varchar(1200)
    ,restoreFilenames varchar(2000))
    DECLARE @fileListTable TABLE (
    [LogicalName] NVARCHAR(128),
    [PhysicalName] NVARCHAR(260),
    [Type] CHAR(1),
    [FileGroupName] NVARCHAR(128),
    [Size] NUMERIC(20,0),
    [MaxSize] NUMERIC(20,0),
    [FileID] BIGINT,
    [CreateLSN] NUMERIC(25,0),
    [DropLSN] NUMERIC(25,0),
    [UniqueID] UNIQUEIDENTIFIER,
    [ReadOnlyLSN] NUMERIC(25,0),
    [ReadWriteLSN] NUMERIC(25,0),
    [BackupSizeInBytes] BIGINT,
    [SourceBlockSize] INT,
    [FileGroupID] INT,
    [LogGroupGUID] UNIQUEIDENTIFIER,
    [DifferentialBaseLSN] NUMERIC(25,0),
    [DifferentialBaseGUID] UNIQUEIDENTIFIER,
    [IsReadOnly] BIT,
    [IsPresent] BIT,
    [TDEThumbprint] VARBINARY(32) -- remove this column if using SQL 2005
    ,allFilesId int
    )
    
    insert into @allFiles (file_nm, depth_no, is_file)
    EXEC Master.dbo.xp_DirTree @folder,1,1
    
    update @allFiles set fullPath = @folder + file_nm
    update @allFiles set restoreFilenames = 'RESTORE FILELISTONLY FROM DISK='''+fullPath+''''
    
    --select id, file_nm, fullPath,restoreFilenames
    --from @allFiles
    
    DECLARE @curFile_id as int, @restoreScript as varchar(2000)
    set @curFile_id = 1
    while @curFile_id <= (select MAX(id) from @allFiles)
    begin
    select @restoreScript=restoreFilenames from @allFiles where id=@curFile_id
    insert into @fileListTable ([LogicalName],[PhysicalName],[Type],[FileGroupName],[Size],[MaxSize],[FileID],[CreateLSN],[DropLSN],[UniqueID],[ReadOnlyLSN],[ReadWriteLSN],[BackupSizeInBytes],[SourceBlockSize],[FileGroupID],[LogGroupGUID],[DifferentialBaseLSN],[DifferentialBaseGUID],[IsReadOnly],[IsPresent],[TDEThumbprint])
    exec(@restoreScript)
    update @fileListTable set allFilesId=@curFile_id where allFilesId is null
    set @curFile_id += 1
    END
    
    select allFilesId, LogicalName, @moveToDrive+SUBSTRING(PhysicalName,2,1000),
    ',MOVE '''+LogicalName+''' TO '''@moveToDrive+SUBSTRING(PhysicalName,2,1000)+''''
    from @fileListTable
    union
    select id, '!'+SUBSTRING(file_nm, 1, PATINDEX('%_FULL%',file_nm)-1),'',
    'RESTORE DATABASE ['+SUBSTRING(file_nm, 1, PATINDEX('%_FULL%',file_nm)-1)+'] FROM DISK='''+fullPath+''' WITH STATS=3,NORECOVERY'
    from @allFiles
    ORDER BY allFilesId, LogicalName
    
    
    
    
    SET NOCOUNT OFF
    Categories: Scripts, SQL Server

    Leave a Reply

    Your email address will not be published. Required fields are marked *