Create a new folder named NewDataFile in the C drive on the primary instance, DPLPR, and the secondary instance, DPLHA.

Execute the following query at the primary instance, DPLPR, to add the new data file to the Sales database:

USE [master]
GO
ALTER DATABASE [Sales] ADD FILE 
( 
  NAME = N'SalesData1', 
  FILENAME = N'C:\NewDataFile\SalesData1.ndf' , 
  SIZE = 8192KB ,
  FILEGROWTH = 65536KB 
) TO FILEGROUP [PRIMARY]
GO

To verify that the file has been created, run the following query on DPLPR:

use [Sales]
GO
SELECT 
  Name,physical_name
FROM sys.database_files

You should get the following output:

Figure 6.46: Verifying that the file has been created

Check the restore job history to find out what the error is. Connect to the DPLHA SQL instance in SSMS. In the Object Explorer, expand SQL Server Agent | Job. Right-click on the LSRestore_Sales job and select View History from the context menu:

Figure 6.47: The View History option

In the Log File Viewer – DPLHA window, observe that the restore job is failing:

Figure 6.48: The Log File Viewer – DPLHA window

Expand a row in the window and start looking for the errors from the bottom row. Observe that the restore job failed with the following error:

*** Error: The file 'C:\DPLPRTlogs\Sales_20181101035136.trn' is too recent to apply to the secondary database 'Sales'.(Microsoft.SqlServer.Management.LogShipping) ***
The log in this backup set begins at LSN 1136000002381600001, which is too recent to apply to the database. An earlier log backup that includes LSN 1136000002379200001 can be restored.

If you look further into the history, the restore operation starts looking for an older file that it can apply to the secondary database. However, it doesn't find any and it terminates with the following error:

*** Error: Could not find a log backup file that could be applied to secondary database 'Sales'.(Microsoft.SqlServer.Management.LogShipping) ***

This is shown in the following screenshot:

Figure 6.49: Output showing the error

Execute the following query at the DPLPR instance against the msdb database:

SELECT 
  bs.database_name,
  bs.backup_start_date,
  bs.type,
  bs.first_lsn,
  bs.last_lsn,
  bmf.physical_device_name

FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf
ON bs.media_set_id = bmf.media_set_id
WHERE database_name='Sales'
AND type='L'
AND first_lsn>=1136000002379200001
ORDER BY backup_start_date

This query looks into the backupset and backupmediaset system tables in the msdb database to fetch all the log files for the Sales database that have LSN greater than or equal to 1136000002379200001.

Here's the output from the query:

Figure 6.50: Output of the query

The LSN that the restore job is expecting is in the backup file named C:\Logshipshare\Sales_adhoc_log_backup.trn. Observe that the backup file that the restore job is failing to apply is taken just after the ad hoc transaction log backup done in step 2 of the problem setup.

We have found the missing log file.

Now, let's restore the missing log backup manually at the secondary instance and then start the restore job to restore the remaining log file backups. To do this, execute the following query at the secondary instance DPLHA:

use master
GO
RESTORE LOG Sales FROM DISK='\\DPLPR\Logshipshare\Sales_adhoc_log_backup.trn' WITH NORECOVERY

Once the missing log backup is restored, you can either wait for the scheduled restore job to restore the rest of the log backups or start the restore job manually.

If you look at the restore job history again, you'll see that the job resumes restoring the logs from where it failed:

Figure 6.51: The restore job history