Auto restore rollup modification.sql

DECLARE @databaseName sysname
DECLARE @backupStartDate datetime
DECLARE @backup_set_id_start INT
DECLARE @backup_set_id_end INT
, @backup_set_id_diff INT
, @filenamereplace nvarchar(max) = '' -- Replace the unc filename with whatever file path you have copied things to

-- set database to be used
SET @databaseName = '' 

SELECT @backup_set_id_start = MAX(backup_set_id) 
FROM  msdb.dbo.backupset 
WHERE database_name = @databaseName AND type = 'D'

SELECT @backup_set_id_diff = MAX(backup_set_id) 
FROM  msdb.dbo.backupset 
WHERE database_name = @databaseName AND type = 'I'

SELECT @backup_set_id_end = MIN(backup_set_id) 
FROM  msdb.dbo.backupset 
WHERE database_name = @databaseName AND type = 'D'
AND backup_set_id > @backup_set_id_diff

IF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999

if @filenamereplace = ''
begin
  SELECT backup_set_id, 'RESTORE DATABASE ' + QUOTENAME(@databaseName) + ' FROM DISK = ''' 
                 + mf.physical_device_name + ''' WITH NORECOVERY'
  FROM    msdb.dbo.backupset b,
             msdb.dbo.backupmediafamily mf
  WHERE    b.media_set_id = mf.media_set_id
             AND b.database_name = @databaseName
            AND b.backup_set_id = @backup_set_id_start
  UNION
  SELECT backup_set_id, 'RESTORE DATABASE ' + QUOTENAME(@databaseName) + ' FROM DISK = ''' 
                 + mf.physical_device_name + ''' WITH NORECOVERY'
  FROM    msdb.dbo.backupset b,
             msdb.dbo.backupmediafamily mf
  WHERE    b.media_set_id = mf.media_set_id
             AND b.database_name = @databaseName
            AND b.backup_set_id = @backup_set_id_diff        
  UNION 
  SELECT backup_set_id, 'RESTORE LOG ' + QUOTENAME(@databaseName) + ' FROM DISK = ''' 
                 + mf.physical_device_name + ''' WITH NORECOVERY'
  FROM    msdb.dbo.backupset b,
             msdb.dbo.backupmediafamily mf
  WHERE    b.media_set_id = mf.media_set_id
             AND b.database_name = @databaseName
            AND b.backup_set_id >= @backup_set_id_diff AND b.backup_set_id < @backup_set_id_end
            AND b.type = 'L'
  UNION
  SELECT 999999999 AS backup_set_id, 'RESTORE DATABASE ' + QUOTENAME(@databaseName) + ' WITH RECOVERY'
  ORDER BY backup_set_id
end
else
begin

  SELECT backup_set_id, 'RESTORE DATABASE ' + QUOTENAME(@databaseName) + ' FROM DISK = ''' 
                 + @filenamereplace + substring(mf.physical_device_name, len(mf.physical_device_name)-(charindex('\', reverse(mf.physical_device_name))-2),len(mf.physical_device_name)) 
                 + ''' WITH NORECOVERY'                               
  FROM    msdb.dbo.backupset b,
             msdb.dbo.backupmediafamily mf
  WHERE    b.media_set_id = mf.media_set_id
             AND b.database_name = @databaseName
            AND b.backup_set_id = @backup_set_id_start
  
  UNION
  SELECT backup_set_id, 'RESTORE DATABASE ' + QUOTENAME(@databaseName) + ' FROM DISK = ''' 
                 + @filenamereplace + substring(mf.physical_device_name, len(mf.physical_device_name)-(charindex('\', reverse(mf.physical_device_name))-2),len(mf.physical_device_name)) 
                 + ''' WITH NORECOVERY'                               
  FROM    msdb.dbo.backupset b,
             msdb.dbo.backupmediafamily mf
  WHERE    b.media_set_id = mf.media_set_id
             AND b.database_name = @databaseName
            AND b.backup_set_id = @backup_set_id_diff        
  UNION 
  SELECT backup_set_id, 'RESTORE LOG ' + QUOTENAME(@databaseName) + ' FROM DISK = ''' 
                 + @filenamereplace + substring(mf.physical_device_name, len(mf.physical_device_name)-(charindex('\', reverse(mf.physical_device_name))-2),len(mf.physical_device_name)) 
                 + ''' WITH NORECOVERY'                               
  FROM    msdb.dbo.backupset b,
             msdb.dbo.backupmediafamily mf
  WHERE    b.media_set_id = mf.media_set_id
             AND b.database_name = @databaseName
            AND b.backup_set_id >= @backup_set_id_diff AND b.backup_set_id < @backup_set_id_end
            AND b.type = 'L'
  UNION
  SELECT 999999999 AS backup_set_id, 'RESTORE DATABASE ' + QUOTENAME(@databaseName) + ' WITH RECOVERY'
  ORDER BY backup_set_id

end