August 4, 2015 at 3:13 am
Hi,
I am in plan to implement following for backup of one of our database
Enable Full recovery mode
1- Create full backup nightly
2- Create transaction log backup after every 25 min
as I am taking full backup every night, I think I can remove transaction log file backups at the time of full backup, as we can apply transaction log backup over full backup.
My question is regarding removal of transaction log backups.
Should I remove all transaction log backups and then execute full backup?
Should I execute full backup and remove all transaction log backup older than 24Hrs ?
Do I have to consider SCN or related info before deleting any transaction log backup ?
Thanks
August 4, 2015 at 5:33 am
(1) No, because if your full backup fails, you'll be left with nothing
(2) Yes, that should work. I usually keep 48 hours of transaction logs, though - it gives more flexibility about what point in time you can restore to
(3) What is SCN?
John
August 4, 2015 at 8:39 am
I will second the comment about keeping transaction log backup files beyond simply the last full backup. What happens if you need to do a point in time restore to before that point? I generally am even more conservative and keep log backups for at least 72 hours. Disk space is cheap, and I'd rather be safe than sorry. It's really all about your recovery objectives.
The simplest thing to do would probably be to simply define retention periods for both log backups and full backups, and let that handle the deletes. This would be really easy to accomplish with something like Ola Hallengren's maintenance scripts.
Senior DBA - Gateway Ticketing Systems
Co-Founder - Do It Simply Software
August 4, 2015 at 8:46 am
Sorry for typo error LSN (Log Sequence Number).
August 4, 2015 at 8:53 am
A transaction log backup is only good so long as the base backup exists (whether full or differential). I've never done differential, but as soon as the full could be deleted, I deleted all the transaction logs up to the next full backup.
As a general rule I kept two full backups plus their transaction logs (for those that had them). For the largest databases, space was an issue and couldn't always do this. I was fortunate that the largest databases were OLAP or OLAP support with a restore setting of SIMPLE.
August 4, 2015 at 9:09 am
Ronkyle,
As you have 2 days of log backups. If you restore latest full backup, do you apply log by looking at create date time or is there some auto way to pick and apply log after full backup restore ?
August 4, 2015 at 9:09 am
You don't normally need to consider the LSN. Just to be safe, it's probably best to retain log backups to an hour, say, before the full backup starts. Then it's there if you need it, but you don't have to use it if you don't. I usually set my retention period for log backups 24 hours longer than that for the full backups, and purge them at the same time as the log backup, not the full. If you are tight on disk space, that may not be an issue for you. Don't forget to use the WITH COMPRESSION option when you make the backups.
John
August 4, 2015 at 9:33 am
As you have 2 days of log backups. If you restore latest full backup, do you apply log by looking at create date time or is there some auto way to pick and apply log after full backup restore
The full backups are appended with YYMMDD. The trans logs with YYMMDD_HHMM. I have a restore script, but it's not automated. When I need to do a restore, I have to do a find a replace on the YYMMDD portion and manually comment out the transaction restores past the point that I need.
August 4, 2015 at 2:37 pm
I guess in this situation, your rentention period should be considered. How much data are you able to lose (if any)? My current backup plan consists of 48 hour logs for the 2 full backups.
August 4, 2015 at 2:45 pm
I know some very highly regarded people that advocate keeping all backups (full, diff, tlog) for at least a month. This will give you the most flexibility and multiple restore paths in the event it's needed.
Now, they don't all have to be online right now. Move them to tape, etc., but keep them available in case they are needed.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 4, 2015 at 2:56 pm
advocate keeping all backups (full, diff, tlog) for at least a month
In too many cases the lose of the most current data for the last month would be catastrophic.
August 5, 2015 at 7:48 am
RonKyle (8/4/2015)
advocate keeping all backups (full, diff, tlog) for at least a month
In too many cases the lose of the most current data for the last month would be catastrophic.
I'm not saying to only take backups once a month. I'm saying to keep those backup files for a least a month. (There was talk here about deleting tlogs after the most recent full.)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply