Very often I need to restore database backups for in-house reproes of issues that I am currently troubleshooting. This can be quite cumbersome if the original database had a bunch of database files. This prodded me along the direction of writing a T-SQL script to help me generate the RESTORE DATABASE command from a RESTORE FILELISTONLY output from a backup file.

 
-- Create the stored procedure to create the headeronly output 
set nocount on go
create proc #sp_restoreheaderonly 
@backuplocation varchar(max) 
as 
begin 
restore filelistonly from disk = @backuplocation 
end 
go 
declare @backuplocation varchar(max),@filelocation varchar(255),@sql varchar(max),@dbname varchar(255) 
set @backuplocation = 'C:\BackupFile.BAK' --Replace with backup file location 
set @filelocation = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\' -- Replace with destination data folder location 
set @dbname = 'RestoredDB' -- Replace with destination database name 

create table #tblBackupFiles
(LogicalName varchar(255),
PhysicalName varchar(255),
Type char(1),
FileGroupName varchar(50),
Size bigint,
MaxSize bigint,
FileId int,
CreateLSN numeric(30,2),
DropLSN numeric(30,2),
UniqueId uniqueidentifier,
ReadOnlyLSN numeric(30,2),
ReadWriteLSN numeric(30,2),
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupId int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN numeric(30,2),
DifferentialBaseGUID uniqueidentifier,
IsReadOnly int,
IsPresent int,
TDEThumbprint varchar(10))

-- Execute above created SP to get the RESTORE FILELISTONLY output into a table

insert into #tblBackupFiles
exec #sp_restoreheaderonly @backuplocation

-- Build the T-SQL RESTORE statement
set @sql = 'RESTORE DATABASE ' + @dbname + ' FROM DISK = ''' + @backuplocation +  ''' WITH STATS = 1, '

select @sql = @sql + char(13) + ' MOVE ''' + LogicalName + ''' TO ''' + @filelocation + LogicalName + '.' + RIGHT(PhysicalName,CHARINDEX('\',PhysicalName)) + ''','
from #tblBackupFiles
where IsPresent = 1

set @sql = SUBSTRING(@sql,1,LEN(@sql)-1)

-- Get the RESTORE DATABASE command

print @sql

-- Cleanup temp objects
drop procedure #sp_restoreheaderonly;
drop table #tblBackupFiles

I created the above script which currently works on SQL Server 2008 and above. The above T-SQL script will look into the Restore Filelistonly output from a backup file and generate the Restore Database command using the Logical and Physical filenames. Another limitation of the above script is that it assumes that there is only 1 database backup in the backup file.