SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


deleting backup logs


deleting backup logs

Author
Message
middletree
middletree
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 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
oded.raz
oded.raz
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 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
middletree
middletree
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 195
thanks. What's the advantage of having more than one .trn file?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)

Group: General Forum Members
Points: 114551 Visits: 45501
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


middletree
middletree
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 195
gotcha. thanks
oded.raz
oded.raz
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)

Group: Administrators
Points: 81844 Visits: 19214
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
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search