delete all Tlog backups following a successful full DB backup

Question

delete all Tlog backups following a successful full DB backup

Edward-445599

Hall of Fame

Points: 3147
More actions
January 26, 2010 at 6:37 am

#138438

Hi,
Is this possible with out scripting a cmd file?
Many thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

there's no pre-configured option in sql to do it.

Why do you want to do it? You would need these log backups to recover to a point in time previous to the last full backup, so have they been copied off somewhere else?

---------------------------------------------------------------------

Edward-445599 Hall of Fame Points: 3147 More actions · Answer 2

I only want to remove all tlogs once a full backup is obtained, rather than deleting t-logs older than a day.

" log backups to recover to a point in time previous to the last full backup" - we are not interested in point in previous time only way t-logs will be used is full backup restore followed by the incremental t-logs, so in this situation once a full backup is obtained old t-logs are no longer required.

I am afraid in the current circumstances’ the question of a sensible approach does not come into it

Many thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Are you sure that you want to do that? What happens if, when going to restore, you find that the latest full DB backup is corrupt? Or find out that there was some bad update done and you need to restore a table to how it was an hour before the last full backup?

Is management/business owner happy with the risk of only being able to restore using the last full backup with no ability whatsoever to restore prior to that and no fallback if the latest backup is damaged?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

GilaMonster (1/26/2010)
Are you sure that you want to do that? What happens if, when going to restore, you find that the latest full DB backup is corrupt? Or find out that there was some bad update done and you need to restore a table to how it was an hour before the last full backup?
Is management/business owner happy with the risk of only being able to restore using the last full backup with no ability whatsoever to restore prior to that and no fallback if the latest backup is damaged?

I must agree with Gail on this point. If you are going to delete all the previous t-log fbackup files, have you backed them up somewhere you can still get to them if needed?

george sibbald SSC Guru Points: 104210 More actions · Answer 5

I agree too.

What are the 'current circumstances'?

---------------------------------------------------------------------

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 6

I tend to agree with Gail on this one. Not sure I'd remove all logs until I had performed a restore with the full to be sure that it works.

We used to remove the log backups prior to the last full so we kept

1. last full backup

2. All log backups between last full and prior full

3. Full backup prior to the last full.