Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Free Learning
Arrow right icon
Arrow up icon
GO TO TOP
Professional SQL Server High Availability and Disaster Recovery

You're reading from   Professional SQL Server High Availability and Disaster Recovery Implement tried-and-true high availability and disaster recovery solutions with SQL Server

Arrow left icon
Product type Paperback
Published in Jan 2019
Publisher Packt
ISBN-13 9781789802597
Length 564 pages
Edition 1st Edition
Languages
Concepts
Arrow right icon
Author (1):
Arrow left icon
Ahmad Osama Ahmad Osama
Author Profile Icon Ahmad Osama
Ahmad Osama
Arrow right icon
View More author details
Toc

Table of Contents (9) Chapters Close

Professional SQL Server High Availability and Disaster Recovery
Preface
1. Getting Started with SQL Server HA and DR FREE CHAPTER 2. Transactional Replication 3. Monitoring Transactional Replication 4. AlwaysOn Availability Groups 5. Managing AlwaysOn Availability Groups 6. Configuring and Managing Log Shipping Appendix

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

lock icon The rest of the chapter is locked
arrow left Previous Section
Register for a free Packt account to unlock a world of extra content!
A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.
Unlock this book and the full library FREE for 7 days
Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of
Renews at $19.99/month. Cancel anytime
Banner background image