deleting backup logs

  • We have a server which houses backups. I am new here and still trying to figure out what the previous DBA did, plus I am not a strong SQL Server person. I'm getting there, thanks to some helpful people here.

    The server which contains the backups is getting full. I am trying to free up some space.

    Here's the folder which takes up the most space.

    I changed the database names to x's...

    06/11/2009 03:00a

    .

    06/11/2009 03:00a

    ..

    06/04/2009 03:09a 3,180,369,408 xxx_backup_200906040300.trn

    06/05/2009 03:06a 1,916,409,344 xxx_backup_200906050300.trn

    06/06/2009 03:05a 1,920,798,208 xxx_backup_200906060300.trn

    06/06/2009 04:10a 3,344,436,736 xxx_backup_200906060400.bak

    06/07/2009 03:00a 525,824 xxx_backup_200906070300.trn

    06/08/2009 03:03a 1,267,765,760 xxx_backup_200906080300.trn

    06/09/2009 03:11a 3,186,087,424 xxx_backup_200906090300.trn

    06/10/2009 03:06a 1,920,096,768 xxx_backup_200906100300.trn

    06/11/2009 03:09a 3,189,056,000 xxx_backup_200906110300.trn

    9 File(s) 19,925,545,472 bytes

    2 Dir(s) 1,704,173,568 bytes free

    My question: is the relatively recent date on those files an indicator that we have something set up in the backup job which deletes stuff older than a week?

    More important, if I delete some of those files manually, do I lose anything?

    thanks

  • 1. SQL Maintenance plan can be configured to delete files older then X days, check up under "Maintenance" tab for existing maintenance plan.

    2. The*.bak files are probably the full backups and the *.TRN are the transaction log backups, probably you can delete the oldest BAK file and the TRN's files that follows until the next BAK files.

    Take a look in the following tutorial and I think after that the backup picture will be clearer for you - http://www.dbsnaps.com/articles/sql_backup_restore

    Oded

    www.orbiumsoftware.com

  • thanks. What's the advantage of having more than one .trn file?

  • To restore to a point in time you need all of the transaction log backups from the full backup up to the point you're restoring to. They are not cumulative.

    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
  • gotcha. thanks

  • In order to restore to the most recent point in time (not only the the last full backup) we have to enable full recovery model.

    In full recovery model the transaction log is not cyclic and the database will hang if there is no more room left in the transaction log. The only way to "clean" the transaction log is to backup it - results in *.trn files, the transaction log is usually backup every X hours to prevent it from runin out of space.

    In order to be able to restore to the most recent point in time we need a full backup and every transaction log backup until current time.

    Oded

    http://www.orbiumsoftware.com

    http://www.dbsnaps.com

  • To add to Gail's note.

    It looks like you have a 4:00am backup. I assume you do that once a day, with log backups taking place every hour.

    If you lose the database at noon, you would restore the 4:00am backup, which gives you data as of 4am. All changes from 7, 8, 9, 10am are gone.

    To restore as close as possible to the issue (user deletes data, corruption, disk crash, etc.), you would need to restore the 5am log backup, the 6am log backup, the 7am log backup, ...., up to the 11am, and the noon log backup (if you have it).

    The 7am log backup does not include stuff from before the 6am log backup. Only stuff from 6am-7am.

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

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