This script is a classic dynamic TSQL.

It uses the MSDB information to build a Restore script with all the backups available on MSDB.

/*
DESCRIPTION:
Looks inside MSDB history information for a particular Database.
Gets the newest FULL backup and Tlogs
and generates a script to restore the Full BK + Diff+ Tlogs
Assumes the backup files are still in original location.
 
Configuration:
@dbname = the DB that we want to restore
@newdbname = The name of the new DB. If no name change, make this values the same, or leave null.
 
Compatibility list: MSSQL2012 MSSQL2005 MSSQL2008 MSSQL2000
 
*/

SET NOCOUNT ON
DECLARE @dbname VARCHAR(500)
DECLARE @newdbname VARCHAR(500)
SET @dbname = 'SALES_ODS'
SET @newdbname = NULL			-- if target DB name will be the same, leave NULL


/*
do not modify below
*/

set @newdbname = coalesce(@newdbname,@dbname)


-- gets the latest FULL backup and all Tlog after that.

DECLARE @bkfiles table
( id int identity(1,1),physical_device_name nVARCHAR (512), [type] char)

INSERT @bkfiles (physical_device_name, [type])
	SELECT m.physical_device_name, s.[type]
	FROM msdb.dbo.backupSET s
	inner join msdb.dbo.backupmediafamily m ON s.media_SET_id = m.media_SET_id
	WHERE
	 s.database_name like @dbname
	 and s.[backup_finish_date] >=
		(SELECT MAX(msdb.dbo.backupSET.backup_finish_date)
		FROM msdb.dbo.backupmediafamily
		INNER JOIN msdb.dbo.backupSET ON msdb.dbo.backupmediafamily.media_SET_id = msdb.dbo.backupSET.media_SET_id
		WHERE msdb..backupSET.type = 'D' and msdb.dbo.backupSET.database_name = @dbname and is_copy_only = '0'
		GROUP BY msdb.dbo.backupSET.database_name
		)
and is_copy_only = '0' -- does not include copy_only backups
 
-- Remove Tlogs and Diff before very last Diff from the table
DELETE FROM @bkfiles
	WHERE ID < (SELECT TOP 1 (ID) FROM @bkfiles
				WHERE TYPE = 'I' ORDER BY ID DESC) 
		and type <> 'D'
-- creates table to hold our Tsql scripts

DECLARE @cmd table
(id int identity(1,1),script NVARCHAR (4000))

INSERT @cmd (script)
	Select 'RESTORE DATABASE ['+@newdbname+'] FROM DISK =N'''+physical_device_name+''''
	from @bkfiles
	where [type]='D'

INSERT @cmd (script)
SELECT 'WITH FILE = 1,'
 
-- Prepare the MOVE Statements

select 'MOVE N'''+name+''''+' to N'''+physical_name+''','  
FROM sys.master_files where database_id=db_id(@dbname) and type_desc = 'ROWS'
 
insert @cmd (script)
select ' NORECOVERY, NOUNLOAD, REPLACE, STATS = 10'

-- Restores the Differential
insert @cmd (script)
select 'RESTORE DATABASE ['+@newdbname+']
FROM DISK = N'''+ physical_device_name +'''
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10'
from @BKFiles
where [TYPE] = 'I'
 
 
-- Build the Tlog chain
insert @cmd (script)
select 'RESTORE LOG ['+@newdbname+']
FROM DISK = N'''+ physical_device_name +'''
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10'
from @BKFiles
where [TYPE] = 'L'

-- Set DB with recovery mode
insert @cmd (script)
select 'RESTORE DATABASE ['+@newdbname+'] WITH RECOVERY'
-- Display the info
select script from @cmd order by id