Restore database in SQL Server TSQL Statement

Here I am trying to explain how we find backup files and related information in SQL Query. Below query shows the top 100 records from backup history order by latest date

SELECT TOP 100 DatabaseName = bs.database_name
	,BackupStartDate = bs.backup_start_date
	,CompressedBackupSize = bs.compressed_backup_size
	,ExpirationDate = bs.expiration_date
	,BackupSetName = bs.name
	,RecoveryModel = bs.recovery_model
	,ServerName = bs.server_name
	,CASE bs.[type]
		WHEN 'D'
			THEN 'Full'
		WHEN 'I'
			THEN 'Differential'
		WHEN 'L'
			THEN 'Transaction Log'
		WHEN 'F'
			THEN 'File or filegroup'
		WHEN 'G'
			THEN 'Differential file'
		WHEN 'P'
			THEN 'Partial'
		WHEN 'Q'
			THEN 'Differential partial'
		ELSE bs.[type]
		END AS BackupType
	,LogicalDeviceName = bmf.logical_device_name
	,PhysicalDeviceName = bmf.physical_device_name
	,'RESTORE LABELONLY FROM DISK = ''' + bmf.physical_device_name + ''' WITH CHECKSUM ' AS ChkSQL
	,'RESTORE ' + CASE bs.[type]
		WHEN 'D'
			THEN 'DATABASE'
		WHEN 'I'
			THEN 'DATABASE'
		WHEN 'L'
			THEN 'LOG'
		END + ' ' + bs.database_name + ' FROM DISK = ''' + bmf.physical_device_name + ''' WITH NORECOVERY' AS 'RestoreSQL'
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
WHERE bs.database_name = '<Database Name>'
ORDER BY bs.backup_start_date DESC;

Replace database name with your database name or remove from query.

Below query containing information about the backup media from backup full file name. Getting from first query.

RESTORE LABELONLY  FROM DISK = 'I:\MSSQL\Backup\dbname\dbname_backup_2020_12_04_090003_0380615.trn' with CHECKSUM 

Below query show database file information from backup full file name. Getting from first query.

RESTORE FILELISTONLY FROM DISK = 'I:\MSSQL\Backup\DBNAME\DBNAME_backup_2020_12_03_200002_1858707.bak'

Returns a result set containing all the backup header information for all backup sets on a particular backup device in SQL Server

RESTORE HEADERONLY   FROM DISK = N'I:\MSSQL\Backup\AdventureWorks-FullBackup.bak';  

The below query creates a temp table and stores file information from the backup file. It will help to restore the database to a different file path.

CREATE TABLE #RestoreFileList (
	RowID INT IDENTITY(1, 1)
	,LogicalName VARCHAR(250)
	,PhysicalName VARCHAR(500)
	,[Type] VARCHAR(2)
	,FileGroupName VARCHAR(100)
	,Size VARCHAR(100)
	,MaxSize VARCHAR(100)
	,FileId INT
	,CreateLSN VARCHAR(250)
	,DropLSN VARCHAR(250)
	,UniqueId VARCHAR(250)
	,ReadOnlyLSN VARCHAR(250)
	,ReadWriteLSN VARCHAR(250)
	,BackupSizeInBytes VARCHAR(250)
	,SourceBlockSize INT
	,FileGroupId INT
	,FileGroupGUID VARCHAR(250)
	,DifferentialBaseLSN VARCHAR(250)
	,DifferentialBaseGUID VARCHAR(250)
	,IsReadOnly VARCHAR(2)
	,IsPresent VARCHAR(2)
	,TDEThumbPrint VARCHAR(250)
	)

INSERT INTO #RestoreFileList (
	LogicalName
	,PhysicalName
	,[Type]
	,FileGroupName
	,Size
	,MaxSize
	,FileId
	,CreateLSN
	,DropLSN
	,UniqueId
	,ReadOnlyLSN
	,ReadWriteLSN
	,BackupSizeInBytes
	,SourceBlockSize
	,FileGroupId
	,FileGroupGUID
	,DifferentialBaseLSN
	,DifferentialBaseGUID
	,IsReadOnly
	,IsPresent
	,TDEThumbPrint
	)
EXEC ('RESTORE FILELISTONLY FROM DISK = ''I:\MSSQL\Backup\DBNAME\DNNAME_backup_2020_12_03_200002_1858707.bak''')

Below query generate restore script with path. replace @newPath and backup filename as per your requirement. You may need to add or remove column depend on SQL server version.

declare @dataFile varchar(1000)='', @newPath varchar(100) ='c:\testpat\'
set @dataFile ='RESTORE DATABASE [signphoto] FROM  DISK = N''I:\MSSQL\signphoto.bak'' WITH  FILE = 1, '
SELECT	@dataFile = @dataFile  + 'MOVE'+''''+LogicalName+''''+' TO '
	+''''+@newPath+'\'+ RIGHT(PhysicalName, CHARINDEX('\', REVERSE(PhysicalName))-1) +''','+CHAR(13)
FROM	#RestoreFileList  
ORDER BY FileId
print @dataFile +',  NOUNLOAD,  STATS = 5'

Generate Restore script as below.

RESTORE DATABASE [signphoto] FROM  DISK = N'I:\MSSQL\signphoto.bak' WITH  FILE = 1, MOVE'signphoto' TO 'c:\testpat\\signphoto.mdf',
MOVE'signphoto_log' TO 'c:\testpat\\signphoto_log.ldf',
,  NOUNLOAD,  STATS = 5

Other References:

https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-headeronly-transact-sql?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-filelistonly-transact-sql?view=sql-server-ver15

Backup History using TSQL

SQL Server database backup history