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.
I would add picking up the list of backups from the disk and generate the script.
Good suggestion. Though the backup on disk may not always be a disk backup or event a native SQL backup. So there are some additional checks that need to be factored in for such a situation. I could though do something at a later time if the backup history was present in MSDB.
Saved me some time tonight, thanks. Needed to move the first “go” to a new line.