Perform full database backup without breaking the backup chain

In today’s time every organization retains a daily full database backup copy on tape. A DBA also make sure that he has full database backup and transactional log backup files to perform a point in time / Full recovery. When there is a major Change Deployment happens on the database a full database backup is mostly done. While all this is in progress DBA forgets what happens If the full backup done in middle of transactional log backup’s and the same is lost thinking it is of no use ,as Transactional backup job is running and performing the Log Backups.

When it comes on restoring the database backup and T-Logs DBA get surprised to see the below error

“System.Data.SqlClient.SqlError: The log in this backup set terminates at LSNXXXXXXXXXXXXXXXXXXXXX, which is too early to apply to the database. A more recent log backup that includes LSN XXXXXXXXXXXXXX can be restored. (Microsoft.SqlServer.Smo)”

On checking the backup tables DBA get more surprised that a full backup of the database is done and the same doesn’t exists to the location. Now only question is; how we will do point in time/Full recovery of the database .Other DBA will answer to this; we have used DB Mirroring rather doing Full, Diff and transactional Log Backup.

What will a DBA do in case an Update statement is executed as” Human Error” on a table which holds the most important data for the organization? How will this be recovered by Database Mirroring? Well it will not.

I will suggest DBA’s the following Strategies which I mostly follow.

  1. Revisit the Organization Backup Policy and check how and when the backup is scheduled and who hold the responsibility.
    1. If the Primary Owner for the Backup is Tape Backup Admin (Datacenter Backup Admin), check how the full backup is done.
      1. File Level Backup (Data File and Log File) —-Cannot Restore the Backup in No Recovery Mode; Point in time / Full recovery is not possible.
      2. SQL Native Backup (SQL Server Format)
      3. LiteSpeed Format
      4. DPM (Data Protection Manager)
      5. Alerts on Failure and Success of Backup
      6. Action Plan on Failure.
    2. If the Primary owner is DBA
      1. What is the backup strategy (Once Full à5 Min Log Backup àEvery 5 hour’s Differential Backupà5 min Log Backup) .Strategy is defined as per Organization business and on Database importance.
      2. Where is the backup done (Disk /Tape)
      3. What is the format of the backup (Compressed /LiteSpeed format)
      4. Alerts on Failure and Success of Backup
      5. Action Plan on Failure.

If you follow any of the above 2 steps always the secondary owner to perform Full Database backup if needed using COPY_ONLY HINT .

A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. For this purpose, copy-only backups were introduced SQL Server 2005. The types of copy-only backups are as follows:

  • Copy-only full backups (all recovery models)A copy-only full backup cannot serve as a differential base or differential backup and does not affect the differential base.
  • Copy-only log backups (full recovery model and bulk-logged recovery model only)A copy-only log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups. Copy-only log backups are typically unnecessary. Instead, you can create another routine, current log backup (using WITH NORECOVERY), and then use that backup together with all other previous log backups that are required for the restore sequence. However, a copy-only log backup can be created for performing an online restore


In the below example i have done a Database Full Backup as part of backup strategy along with Transactional Log Backup and in the middle done a Database Full Backup again using COPY_ONLY HINT to provide the same to develpment team.


Restore the Database Full backup and TLog Backup part of Backup strategy.

Leave a Reply

Your email address will not be published.