Cleaning up transaction Log Backups

TYzermans

Hello,

Our Navision database is hosted on a SQL Server 2005. The recovery model is set to full. A maintenance plan performs a full daily database backup at night. During the day there are multiple transaction log backups scheduled.

The full database backups are kept 14 days.

Today I noticed that the transaction log backups are also stored for 14 days. Is it of any use to store the transaction log backups for more then 1 day when performing a daily full backup? (because, and correct me if I'm wrong, the transaction logs contains al changes to the database since the last succesfull full database backup).

Does it make any difference in the ability to restore any data when you save thes log backups longer?

Thanks in advance,

Best Regards,
Timothy Yzermans

DenSter

Only in a theoretical instance. Say you discover that something corrupted your database at 11:45am two days ago, and you decide you want to restore the database back to the minute before that. You would need the full backup of the day before and all TL backups up to the moment you want to restore. Obviously if you decide to save TL backups for only one day, that won't be possible.

It really depends on how much detail you want, and over what kind of period you want it. I'd say that keeping 14 days worth of TL backups is probably a bit too much, but it does make it so that you can restore it to any point in time over the past 14 days. The more you trim down on what you save, the less you will be able to restore. The decision about what to save should be made by the people that own the data though, it's not a technical decision.

Alex_Chow

Wait a minute, this kind of question is on the SQL exams!

The answer is no. If you keep a full backup nightly, you do not have to keep the transaction log for the prior day. This is assuming, of course as Denster said, that your backup doesn't fail.

Say your database became corrupt at 11:45pm on 1/15/09. You would need to restore:
1. Full database backup from 1/14/09
2. All the TLOG backup from 1/14/09 from 1:00am to 11:00pm (assuming you backup TLOG every hour).

garak

it's so easy to get the answers :-D

(is there nobody who reads books or anything else before :-# )

TYzermans

DenSter wrote:

Only in a theoretical instance. Say you discover that something corrupted your database at 11:45am two days ago, and you decide you want to restore the database back to the minute before that. You would need the full backup of the day before and all TL backups up to the moment you want to restore. Obviously if you decide to save TL backups for only one day, that won't be possible.

And when I restore the data into another temporary database? Doesn't that give me more options for restoring data?

Alex Chow wrote:

This is assuming, of course as Denster said, that your backup doesn't fail.

Hmm, that's indeed a situation to keep in mind. Unless... I now have 4 seperate maintenance plans. 2 for the backup jobs (full DB + TL) and another 2 for cleaning up the history. When I replace the MPs for the "full DB backup" and "TL-backup File cleanup" with 1 plan with 2 subplans where the first would be the one doing a full DB Backup and the second subplan for cleaning up the TL-backups files with the clausule that the job must end/abort when the first subplan should fail. In that case the TL-backup files won't be deleted when the full backup job fails, right?

Thanks for the advice guys.

Best regards,
Timothy Yzermans

Alex_Chow

When you do a backup for the TLOG, it will automatically clear the TLOG, why do you need a script to clear it?

DenSter

Alex Chow wrote:

The answer is no. If you keep a full backup nightly, you do not have to keep the transaction log for the prior day.

Say your database became corrupt at 11:45pm on 1/15/09. You would need to restore:
1. Full database backup from 1/14/09
2. All the TLOG backup from 1/14/09 from 1:00am to 11:00pm (assuming you backup TLOG every hour).

I think you're oversimplifying things here Alex. What if a transaction is corrupted on 1/14 at 2PM, and you don't discover it until 2 days later. Deleting the TL backups after 1 day will make it so you won't be able to restore back to the vcery minute of that transaction.

Your assumption is that as soon as you have a full backup for moment in time X, all TL backups that were made before that become obsolete, which I don't necessarily agree with. This depends on what you want to achieve, and what your attitude is toward data loss.

Alex Chow wrote:

When you do a backup for the TLOG, it will automatically clear the TLOG, why do you need a script to clear it?

I think he means a job that clears the old TL backup files

DenSter

TYzermans wrote:

And when I restore the data into another temporary database? Doesn't that give me more options for restoring data?

No I don't think that has anything to do with it. Restoring a full backup creates another copy of the database as it was when you took the backup. You can't replay transactions from one database into another.

TYzermans wrote:

Hmm, that's indeed a situation to keep in mind. Unless... I now have 4 seperate maintenance plans. 2 for the backup jobs (full DB + TL) and another 2 for cleaning up the history. When I replace the MPs for the "full DB backup" and "TL-backup File cleanup" with 1 plan with 2 subplans where the first would be the one doing a full DB Backup and the second subplan for cleaning up the TL-backups files with the clausule that the job must end/abort when the first subplan should fail. In that case the TL-backup files won't be deleted when the full backup job fails, right?

When you create the maintenance plan that creates the backups, there is an option in there that says "keep backups for x days", and it will automatically delete older files when a new backup is created. You don't need a separate job for that, unless of course you just feel more comfortable doing it that way.

There are some excellent articles about backup strategies in SQL Server Books Online. From the Start menu, All programs, SQL Server 2005, Documentation and Tutorials, you will find SQL Server books online. Go to the search tab and search for the word backup, or backup strategies, maintenance plan, thigns like that. There is a ton of information in there.

Alex_Chow

DenSter wrote:

I think you're oversimplifying things here Alex. What if a transaction is corrupted on 1/14 at 2PM, and you don't discover it until 2 days later. Deleting the TL backups after 1 day will make it so you won't be able to restore back to the vcery minute of that transaction.

This is why I state this in my original post:

This is assuming, of course as Denster said, that your backup doesn't fail.

On another note, the question posed here is on the SQL certification test and also on the Microsoft Press training material. So study hard if you plan to pass 70-431 or 70-432!

DenSter

Alex Chow wrote:

This is why I state this in my original post:

This is assuming, of course as Denster said, that your backup doesn't fail.

I wasn't talking about failing backups, and it is irrelevant whether that backup fails or not in the scenario I mentioned.

Let's say you have Full backup 1, TL backup, TL, x, TL, TL, Full backup 2, TL, TL, TL, TL. Let's say you want to restore to point x. Whether the second full backup failed or not is irrelevant. My point is that if you want to be able to restore to that point, you don't delete those TL backups.

Your point is that as soon as you have full backup 2, then all TL backups before that become obsolete, and you can delete the previous ones right away. I don't think you can say that, because those TL backups serve a purpose.

bbrown

Alex Chow wrote:

Wait a minute, this kind of question is on the SQL exams!

The answer is no. If you keep a full backup nightly, you do not have to keep the transaction log for the prior day. This is assuming, of course as Denster said, that your backup doesn't fail.

Say your database became corrupt at 11:45pm on 1/15/09. You would need to restore:
1. Full database backup from 1/14/09
2. All the TLOG backup from 1/14/09 from 1:00am to 11:00pm (assuming you backup TLOG every hour).

Let's say the DB became corrupt at 11:45 PM. Depending on what the corruption was and if you still have access to the db catalog:

1. Backup the active log tail
2. Restore full backup from 1/14/09 with NORECOVERY option
3. Restore all Tlog backups to 11:00 PM with NORECOVERY
4. Restore the log tail using STOPAT to 11:45 PM and recover

You know have a database restored to the point of failure.

Alex_Chow

DenSter wrote:

Let's say you have Full backup 1, TL backup, TL, x, TL, TL, Full backup 2, TL, TL, TL, TL. Let's say you want to restore to point x. Whether the second full backup failed or not is irrelevant. My point is that if you want to be able to restore to that point, you don't delete those TL backups.

Your point is that as soon as you have full backup 2, then all TL backups before that become obsolete, and you can delete the previous ones right away. I don't think you can say that, because those TL backups serve a purpose.

Ok, I think we're talking about 2 different things. I'm talking about backing up in case of database failure. If the user is strictly talking about backing up to restore in case of database failure, why keep the TLOG after a full back was done?

You're right in the statement that old TLOG serves different purposes. But the original posting didn't really specify what the backup is used for. I just made an assumption that it was used to restore database after it's corrupted.

What if a transaction is corrupted on 1/14 at 2PM, and you don't discover it until 2 days later. Deleting the TL backups after 1 day will make it so you won't be able to restore back to the vcery minute of that transaction.

Perhaps I haven't seen enough SQL implementations, but I never encountered a situation where a corrupt transaction in NAV was not detected immediately.

Alex_Chow

bbrown wrote:

Let's say the DB became corrupt at 11:45 PM. Depending on what the corruption was and if you still have access to the db catalog:

1. Backup the active log tail
2. Restore full backup from 1/14/09 with NORECOVERY option
3. Restore all Tlog backups to 11:00 PM with NORECOVERY
4. Restore the log tail using STOPAT to 11:45 PM and recover

You know have a database restored to the point of failure.

#-o You're absolutely correct.. Time to hit the books again...

DenSter

Alex Chow wrote:

I think we're talking about 2 different things

Yes definately, and it feels to me that you are not really trying to understand my point.

From the original post:

TYzermans wrote:

Does it make any difference in the ability to restore any data when you save thes log backups longer?

To which I replied the answer is mainly theoretical, and I gave an example of when you would need to use older TL backups, and stated that it depends on the attitude of the user.

For whatever reason you would want to be able to restore to any specific point between two full backups, you would need to have those TL backups. You can't just say that TL backups become obsolete when you take a full backup, and that you can delete them as soon as you take a full backup, that is simplifying it too much.