1

What are the ramifications of having deleted a backup of the SQL Server transaction log for a database that's in Full Recovery mode?

Caveat: I'm a developer, not a DBA, and we're a "one person" shop.

The saga: We were running out of space on our hard drive, needed to refresh Staging, and didn't have enough room to do so. I took a transaction log backup (which apparently had not been done in a long time, if ever), tried to Shrink the trans log, but that only reduced the size by about 40GB instead of the 175GB it was taking up. I then didn't have enough room for the upcoming nightly backup, so - in my endless wisdom - I deleted the TLOG backup I'd just taken. Restored a copy of Prod to Staging using the 40GB I'd freed up by shrinking the transaction log the first time. The nightly production backups succeeded and all was well. Then this morning I took another backup of the Prod transaction log and tried to shrink it again. This time it worked and free up most of the 135GB that it was taking up.

So... have I potentially ruined the integrity of my Production database? What if in a few days I encounter a disaster and need to do a restore... will the fact that I deleted the first transaction log backup prevent me from doing that?

Improve this question
2
  • 2
    What do your nightly production backups do? FULL/DIFF backups of your databases? If a FULL/DIFF backup has not been taken since you deleted the log backup, you should take at least a DIFF immediately. Deleting the transaction log backup means you won’t be able to restore to a point in time encompassing the timeframe of the deleted log up to a new FULL/DIFF afterwards. I’m assuming you’re still in FULL recovery mode.
    – Scott Hodgin - Retired
    Commented Jan 26, 2018 at 13:59
  • 1
    Check out the Ola Hallengren backup scripts. They are very verbose and fit well with any backup solution you might deploy. Plus they are free.
    – clifton_h
    Commented Jan 26, 2018 at 14:57

2 Answers 2

Reset to default
1

So order of operation:

  1. 1st tlog backup
  2. delete 1st tlog backup
  3. full backup
  4. tlog backup
  5. no catastrophe, just concerned over integrity

If this is correct then you will be fine. As long as the LSN or chain of (backup) events since the last full backup is not broken you should have no issues with recovery.

Improve this answer
0

Your full backup will be the new starting point for your log chain; any backups before that (full, t-log or otherwise) will be useless unless you want to restore to an earlier point-in-time. A full backup is just that, a 100% complete backup of your database that will guarantee the database can be restored exactly as-is from the starting point of your backup; e.g. if you begin to backup the database at 3pm and it takes an hour for the full backup to complete, you can guarantee that if you need to restore the database to recover from disaster, all transactions that were committed by 3pm will be fully available via the restore. Transaction log backups only serve to capture the activity since the previous full or differential backup.

Improve this answer

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.