1

When ever we delete a DB or backup and restore a DB, we get email alert that our hourly transaction log has failed (log is taken every 30 minutes). Every log after fails, to fix the issue we have to manually run the Full DB backup job which is run every day and the logs start working again.

This is very frustrating. Why is this happening and how can we prevent it from occurring?

We are using MS SQL Server 2016 and MS SQL Server 2019

2
  • 3
    Post the complete error message. Without context all that can be said is that you broke the lsn chain for log backups and it must be re-initialized with a new full backup. And note that "delete a DB" is a very different sort of thing from "delete ... or backup" which is very different from "restore a DB". Quite frankly it seems like whoever is managing this instance needs some additional training or help. Maybe full recover model is just not appropriate for some of these databases (or the instance)?
    – SMor
    Commented Nov 15, 2021 at 20:30
  • 2
    we have to manually run the Full DB backup job yes you do. A transaction log backup is useless on its own. A transaction log backup copies the log entries since the last full or transaction log backup, so they can be replayed during restore after the previous backups are restored. You're starting with a new database so there's no previous backup to use as a baseline Commented Nov 16, 2021 at 9:22

2 Answers 2

4

This is entirely expected.

From the docs:

Before you can create the first log backup, you must create a full backup.

This is because you have broken the LSN chain. Whenever you create or replace a DB in full recovery mode, you must take a full backup immediately to be able to take a log backup of that database. Other databases are not affected though.

You can use the following query to determine if you can take a log backup or not

select
  case when d.recovery_model = 3 or last_log_backup_lsn is not null
    then 1 else 0 as IsBackupable
from sys.databases d
join sys.database_recovery_status r on r.database_id = d.database_id
where d.database_id > 4  -- not system
  and d.state = 0  -- "online"
;
2
  • This is quite annoying, will the chain also break when we add a new DB on the server? I created anew DB and got the error for job fail. I ran full backup and think it fixed issue as I got no alerts. I created another DB later on and got alert. There is no way to stop this happening so we don't have to keep running full backup jobs? Most times I don't get an error, only happened today.
    – Sam
    Commented Nov 22, 2021 at 21:52
  • 1
    This is per database. If you add a new database, yes you need to take a full backup of it to start the chain. A good script or tool would check if a full backup needs to be run first, it's the basics of database backups. I suggest you take a look at whatever it is that you are using and correct this problem. You can use the following query to get last backup time for DBs in full recovery select last_log_backup_lsn from sys.database_recovery_status Commented Nov 23, 2021 at 0:05
0

What backup solution are you using?

Some vendors allow you to raise the backup level depending on the state of the database.

Ola Hallengren's Maintenance Solution

Ola Hallengren's maintenancesolution.sql has a parameter named ChangeBackupType for the SQL Server Backup procedure which is documented as:

@ChangeBackupType
Change the backup type if a differential or transaction-log backup cannot be performed.

So if you create a database or restore a database and no previous FULL backup exists, then the procedure will automatically raise the TLOG backup to a FULL backup.

Reference: SQL Server Backup (Ola Hallengren | SQL Server Maintenance Solution)

Commvault Simpana - SQL Server Sublcient

Commvault's SQL Server Subclient has the configuration option of Automatic Conversion of Non-Full Backups into Full Backups

Automatic Conversion of Non-Full Backups into Full Backups

  • First backup operation of a subclient.
  • Backup operation run on databases after the following restore jobs were run on those databases:
    • Point in time restore
    • Transaction mark restore
    • Partial or Piecemeal restore
  • Backup operation run on a database after switching the recovery model for that database from simple to either full or bulk-logged.

Reference: Automatic Conversion of Non-Full Backups into Full Backups (Commvault Support / Login required)

Your Environemnt

Depending on your backup solution/provider/vendor you will want to have the backup level automatically raised from TLOG to FULL as soon as you:

  1. restore a database
  2. create a new database

This will ensure that you have a FULL backup and no errors.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.