Lesser Known SQL Server Backup Practices

By: Tim DelChiaro

Abstract: A look at some of the common mistakes and possible mitigations for the error messages one encounters

One of the most critical responsibilities of a Database Administrator (DBA) is to make sure that databases are backed up on regular basis. What are your thoughts on creating backup strategies for your production environments? We would surely want to learn and understand the various ways in which organizations use backup strategies. One thing which is often missed by DBAs is to check the validity of the backup plan. Few DBAs do this religiously by practicing the disaster recovery plan and recovery procedure by doing DR Drill. Sometimes junior DBAs who may not have clarity about the basics of database recovery models and backup do mistakes. This blog post looks at some of the common mistakes one does and the possible mitigations for these error Hide image
Plan Stability Webinar On Demandmessages one encounters.

Imagine a scenario where log shipping is configured for the database and things are going well. One fine day an accidental DBA arrives, opens SSMS and find that they don’t have maintenance plan created for transaction log backup of this critical database. He configures new maintenance plan to take log backup. What would happen to log shipping? Have you ever got into this scenario. We found this asked in one of the forums and felt this is worth a note as a blog post. Since log backup can be restored only in sequence, the log restore on secondary database (part of Log shipping) would fail with below error:

2014-09-10 05:23:13.63 *** Error: The file 'e:\ls\copy\Finance_20140910233000.trn' is too recent to apply to the secondary database 'Finance'.(Microsoft.SqlServer.Management.LogShipping) ***

2014-09-10 05:23:13.64 *** Error: The log in this backup set begins at LSN 54000000045600001, which is too recent to apply to the database. An earlier log backup that includes LSN 54000000044800001 can be restored.

RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***

2014-09-10 05:23:13.64 Searching for an older log backup file. Secondary Database: 'Finance'

Same error of “too recent to apply to the database” can appear in disaster recovery situations as well. Imagine a situation if a DBA configures two different log backup jobs are and both are taking backup to different location.

  Typical Solution – recommended steps

What accidental DBA should have done to avoid such scenario? Well, there are multiple options in hand:

a)      Check ErrorLog file to see if there are backup messages. Every successful backup write messages like below in ERRORLOG


<Date Time>   Backup       Log was backed up. Database: Finance, creation date(time): 2010/06/11(18:39:36), first LSN: 54:520:1, last LSN: 54:536:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\LS\Finance_20140822001500.trn'}). This is an informational message only. No user action is required.

Most important piece here is the path where backup is taken. Above message is for transaction log backup. Similar message are printed for full and differential backups as well. Here is the message for full backup.

 

<Date Time>   Backup       Database backed up. Database: Finance, creation date(time): 2010/06/11(18:39:36), pages dumped: 307, first LSN: 54:392:37, last LSN: 54:424:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\Finance\Full.bak'}). This is an informational message only. No user action is required.


and this one for differential backup:

 

2014-09-10 06:44:09.370 Backup       Database differential changes were backed up. Database: Finance, creation date(time): 2010/06/11(18:39:36), pages dumped: 51, first LSN: 54:600:35, last LSN: 54:632:1, full backup LSN: 54:392:37, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\Kooda\Backup_Diff.bak'}). This is an informational message. No user action is required.

Hide image
Click to see full-sized image

  1. Check if MSDB database has information about backup of the given database. Below query can be used (taken via SSMS report)

Declare @DatabaseName sysname

set @DatabaseName = 'Finance'

use msdb;

Select distinct

        t3.user_name

,       t3.name as backup_name

,       t3.description

,       (datediff( ss, t3.backup_start_date, t3.backup_finish_date))/60.0 as duration

,       t3.backup_start_date

,       t3.backup_finish_date

,       t3.type as [type]

,       case when (t3.backup_size/1024.0) < 1024 then (t3.backup_size/1024.0)

                when (t3.backup_size/1048576.0) < 1024 then (t3.backup_size/1048576.0)

        else (t3.backup_size/1048576.0/1024.0)

        end as backup_size

,       case when (t3.backup_size/1024.0) < 1024 then 'KB'

                when (t3.backup_size/1048576.0) < 1024 then 'MB'

        else 'GB'

        end as backup_size_unit

,       t3.first_lsn

,       t3.last_lsn

,       case when t3.differential_base_lsn is null then 'Not Applicable'

        else convert( varchar(100),t3.differential_base_lsn)

        end as [differential_base_lsn]

,       t6.physical_device_name

,       t6.device_type as [device_type]

,       t3.recovery_model 

,       t3.backup_set_id

from sys.databases t1

inner join backupset t3 on (t3.database_name = t1.name ) 

left outer join backupmediaset t5 on ( t3.media_set_id = t5.media_set_id )

left outer join backupmediafamily t6 on ( t6.media_set_id = t5.media_set_id )

where (t1.name = @DatabaseName)

order by backup_start_date desc,t3.backup_set_id,t6.physical_device_name

Above methods would help in finding if there was a backup happening on this Instance on SQL for a database which can save a DBA from making mistake of duplicate backups.

In the situations where log shipping is out of sync due to additional out-of-band transaction log backup, above methods can help in finding rogue backup. DBA can restore the backup manually and log shipping would catch up automatically for subsequent backups.

Understanding the backup strategy of your organization before implementing a new technique. Yes, for databases that don’t have a backup, as a proactive maintenance plan would surely be a correct idea. But in this situation it went haywire because there were other dependencies on backup sequence.

Click to learn more about Embarcadero database tools related to this post and more from Pinal Dave:


Hide image

About Pinal Dave
Technology Evangelist & Founder of SQL Authority

Pinal Dave works as a Technology Evangelist (Database and BI) with Microsoft India. He has written over 2000 articles on the subject on his blog at http://blog.sqlauthority.com. During his career he has worked both in India and the US, mostly working with SQL Server Technology – right from version 6.5 to its latest form. Pinal has worked on many performance tuning and optimization projects for high transactional systems. He has been a regular speaker at many international events like TechEd, SQL PASS, MSDN, TechNet and countless user groups.

   

Server Response from: ETNASC01