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.
TroubleshootingSQL 