One of the most critical responsibility of a Database Administrator (DBA) is to make sure that the 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 backup plan created. 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 is to bring some of the common mistakes one does and what are the possible mitigations for these error messages 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:lscopyFinance_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:LSFinance_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:FinanceFull.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:KoodaBackup_Diff.bak’}). This is an informational message. No user action is required.
b) Check backup and restore events report for this database
c) 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.