deleting backup logs

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Backups » deleting backup logs

deleting backup logs

Rate Topic

Display Mode

Topic Options

Author

Message

middletree

Posted Thursday, June 11, 2009 1:18 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 09, 2010 7:58 PM
Points: 121, Visits: 195

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

Post #733287

oded.raz

Posted Thursday, June 11, 2009 1:38 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, November 10, 2009 11:23 AM
Points: 13, Visits: 31

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

Post #733302

middletree

Posted Thursday, June 11, 2009 2:05 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 09, 2010 7:58 PM
Points: 121, Visits: 195

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

Post #733324

GilaMonster

Posted Thursday, June 11, 2009 2:11 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 38,079, Visits: 30,373

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 2008, MVP
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

Post #733329

middletree

Posted Thursday, June 11, 2009 2:13 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 09, 2010 7:58 PM
Points: 121, Visits: 195

gotcha. thanks

Post #733332

oded.raz

Posted Thursday, June 11, 2009 2:14 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, November 10, 2009 11:23 AM
Points: 13, Visits: 31

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

Post #733333

Steve Jones - SSC Editor

Posted Thursday, June 11, 2009 2:15 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 5:09 AM
Points: 31,526, Visits: 13,864

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.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #733335

« Prev Topic | Next Topic »

Permissions