SQLServerCentral / SQL Server 2005 / Backups / deleting backup logs / Latest Posts

RE: deleting backup logs

Steve Jones - SSC Editor — Thu, 11 Jun 2009 14:15:37 GMT

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.

RE: deleting backup logs

oded.raz — Thu, 11 Jun 2009 14:14:00 GMT

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.Odedwww.orbiumsoftware.comwww.dbsnaps.com

RE: deleting backup logs

middletree — Thu, 11 Jun 2009 14:13:53 GMT

gotcha. thanks

RE: deleting backup logs

GilaMonster — Thu, 11 Jun 2009 14:11:18 GMT

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.

RE: deleting backup logs

middletree — Thu, 11 Jun 2009 14:05:10 GMT

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

RE: deleting backup logs

oded.raz — Thu, 11 Jun 2009 13:38:41 GMT

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_restoreOded[url]www.orbiumsoftware.com[/url]

deleting backup logs

middletree — Thu, 11 Jun 2009 13:18:52 GMT

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.trn06/05/2009 03:06a 1,916,409,344 xxx_backup_200906050300.trn06/06/2009 03:05a 1,920,798,208 xxx_backup_200906060300.trn06/06/2009 04:10a 3,344,436,736 xxx_backup_200906060400.bak06/07/2009 03:00a 525,824 xxx_backup_200906070300.trn06/08/2009 03:03a 1,267,765,760 xxx_backup_200906080300.trn06/09/2009 03:11a 3,186,087,424 xxx_backup_200906090300.trn06/10/2009 03:06a 1,920,096,768 xxx_backup_200906100300.trn06/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 freeMy 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