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
