Converting Restore Filelistonly command into Restore Database command

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) 
restore filelistonly from disk = @backuplocation 
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.


Generating SELECT INTO scripts for all user tables

Often on data recovery cases, we need to extract all the user table data into a new database. There are multiple options to perform this task:

1. Export/Import Wizard

2. Custom SSIS Task

3. Copy Database Wizard

4. BCP operations

5. SELECT INTO T-SQL commands

I find that the last option is the easiest. I use the following script to get all the T-SQL SELECT…INTO commands for all user tables in the old database:

 select 'select * into <new db>.''.[''] from <old db>.''.['']' from sys.objects a inner join sys.schemas b on a.schema_id = b.schema_id where a.type = 'U' 

Note: You will have to replace the new database and old database names with the appropriate names. The above script only works for versions of SQL Server 2005 and above.