Lesson 6: Configuring and Managing Log Shipping

Activity 7: Adding a New Data File to a Log Shipped Database

Solution:

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

2. 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

Activity 8: Troubleshooting a Problem – Could Not Find a Log Backup File that Could be Applied to Secondary Database 'Sales'

Solution:

Finding the Error Details

1. 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

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

   

   Figure 6.48: The Log File Viewer – DPLHA window

3. 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

Resolution

This is because of the ad hoc backup taken at step 2 in the problem setup. Every transaction log backup contains the first and last LSN as header information. SQL Server maintains a history of all the database backups that are performed on an instance (native or using third-party tools) in the msdb database. We know the LSN of the log file that's missing; let's query the backup metadata tables in msdb and find out the missing log file:

1. 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

   Note

   The LSN value will be different in your case.

   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.

2. 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

   Note

   The WITH NORECOVERY option is important as we want to restore future transaction log backups to the Sales database.

   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