June 11, 2009 at 1:18 pm
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
June 11, 2009 at 1:38 pm
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
June 11, 2009 at 2:05 pm
thanks. What's the advantage of having more than one .trn file?
June 11, 2009 at 2:11 pm
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
June 11, 2009 at 2:13 pm
gotcha. thanks
June 11, 2009 at 2:14 pm
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
June 11, 2009 at 2:15 pm
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