SET NOCOUNT ON;
DECLARE @DatabaseName SYSNAME = 'AdventureWorks';
BACKUP DATABASE @DatabaseName TO DISK = '\\FileServer\FileShare\RemoteCheckDBPractice.bak';

DECLARE @FullBackupName NVARCHAR(520);
DECLARE @DiffBackupName NVARCHAR(520);
DECLARE @backup_set_id INT;

SELECT TOP 1
  @FullBackupName = fam.physical_device_name
, @DiffBackupName = famDIFF.physical_device_name
, @backup_set_id = bset.backup_set_id
FROM 
  msdb.dbo.backupset bset 
    INNER JOIN
  msdb.dbo.backupmediafamily fam ON bset.media_set_id = fam.media_set_id
    LEFT OUTER JOIN
  msdb.dbo.backupset bsetDIFF ON bset.backup_set_uuid = bsetDIFF.differential_base_guid AND bsetDiff.type = 'I'
    LEFT OUTER JOIN
  msdb.dbo.backupmediafamily famDIFF ON bsetDIFF.media_set_id = famDIFF.media_set_id
WHERE 
  bset.type = 'D'
    AND
  bset.server_name = @@SERVERNAME
    AND
  bset.database_name = @DatabaseName
    AND
  bset.is_copy_only = 0
ORDER BY 
  bset.backup_finish_date DESC
, bsetDIFF.backup_finish_date DESC;


SELECT 
  logical_name
, file_type
, file_size/1024/1024 AS FileSizeMB
FROM 
  msdb.dbo.BackupFile 
WHERE 
  backup_set_id = @backup_set_id;

PRINT '--Run these commands on the secondary box';

DECLARE @SQL NVARCHAR(4000);
DECLARE @TargetDataFilePath NVARCHAR(520);
DECLARE @TargetLogFilePath NVARCHAR(520);
SET @TargetDataFilePath = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS NVARCHAR(520))
SET @TargetLogFilepath = CAST(SERVERPROPERTY('InstanceDefaultLogPath') AS NVARCHAR(520))

SET @SQL = 'RESTORE DATABASE ' + QUOTENAME(@DatabaseName) + ' FROM DISK = ''' + @FullBackupName + ''' WITH ';
  
SELECT @SQL = @SQL + 'MOVE ''' + logical_name + CASE WHEN file_type = 'D' THEN ''' TO ''' + @TargetDataFilePath + logical_name + '.' + CASE WHEN file_number = 1 THEN 'm' ELSE 'n' END + 'df'',' 
                                                                          ELSE ''' TO ''' + @TargetLogFilePath + logical_name + '.ldf'','
                                                  END
FROM 
  msdb.dbo.backupfile
WHERE 
  backup_set_id = @backup_set_id;
 
--We will not recover the database in case a differential needs to be applied    
SET @SQL = @SQL + ' NORECOVERY;';
 
PRINT @SQL;
--EXEC sp_executeSQL @SQL;
 
--Restore DIFF, if necessary
IF @DiffBackupName IS NOT NULL
BEGIN
  SET @SQL = 'RESTORE DATABASE ' + QUOTENAME(@DatabaseName) + ' FROM DISK = ''' + @DiffBackupName + ' WITH ';
      
SELECT @SQL = @SQL + 'MOVE ''' + logical_name + CASE WHEN file_type = 'D' THEN ''' TO ''' + @TargetDataFilePath + logical_name + '.' + CASE WHEN file_number = 1 THEN 'm' ELSE 'n' END + 'df'',' 
                                                                          ELSE ''' TO ''' + @TargetLogFilePath + logical_name + '.ldf'','
                                                  END
  FROM 
    msdb.dbo.backupfile
  WHERE 
    backup_set_id = @backup_set_id;
      
  --We still don't recover the DB so that we can be certain of its condition later on
  SET @SQL = @SQL + ' NORECOVERY, REPLACE;';
  PRINT @SQL;
  --EXEC sp_executeSQL @SQL;
END;
    
 
--RECOVER the database since we are certain it has not been recovered
PRINT 'RESTORE DATABASE ' + QUOTENAME(@DatabaseName) + ' WITH RECOVERY;';
 
PRINT 'DBCC UPDATEUSAGE(' + @DatabaseName + ');';

PRINT 'DBCC CHECKDB (' + @DatabaseName + ') WITH TABLOCK;';
GO