truncate_only
-
Hi,
When backing up a transaction log with truncate_only, is it better to backup the database before or after executing this command? In Books Online, it says to backup the database immediately AFTER executing truncate_only, however, in an SQL Server book I am reading (DBA Survival Guide) it says to backup the database BEFORE executing truncate_only.
tks.
-
The advice to backup the database after doing a truncate_only is because truncate_only removes checkpointed transactions from the log and therefore you have lost your ability to restore to a point in time since your last full backup.
Personally I would backup before AND after
-
You decide. But here is some information on which to base your decision.
Before. Doing a full backup before truncating, ensures that you can return the database to its original state if something goes wrong with the truncate. (Example, you mistype and truncate the wrong thing).
After. Truncating the log 'breaks' the backup chain. Since transaction log backups must be restored in the same order they were made, the truncate effectively causes one tlog backup to be skipped. So following tlog backups do not 'belong' to the chain. A full backup restarts the chain of backups.
Depending on how long your backups take, I would follow David Poole's advice. However, for my database of 280GB, it would take too long. I do one last tlog backup and then truncate. If I need to restore to before the truncate, I restore the full backup and all the tlog backups including that final one. I also do a full backup IMMEDIATELY after I truncate the tlog.
-SQLBill
-
Just as a matter of interest SQLBill do you use the straight SQL Backup or do you use a tool such as SQL Litespeed?
-
I use the native SQL Server backup commands.
-SQLBill
-
No one mentioned anything about 'recovery mode' ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Rudy,
What does 'recovery mode' have to do with the poster's question? This is what the poster asked:
When backing up a transaction log with truncate_only, is it better to backup the database before or after executing this command?
-SQLBill
-
It makes a difference in how you can recover. If recovery mode is 'simple' than a backup before the truncate is not needed at all, just after the truncation to maintain recoverabilty. That is because 'truncate_only' only removes completed transactions. Those still in progress are still in the log so automatic recovery can keep your database in sync. On the other hand if recovery mode 'bulk logged' or 'full' it is probably best to backup the transaction log (maintaining your up to the minute recovery), truncate the log, then perform the full database backup to maintain your ability to recover up to the minute moving forward. Does that help a bit SQLBill ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Good points Rudy. The poster is obviously in FULL or Bulk-logged mode (can't do transaction log backups in SIMPLE mode - even just using TRUNCATE_ONLY - I tried it once).
I prefer doing a full backup before truncating, but you make a very valid point about doing a transaction log backup prior to truncating. It would probably take less time than a full backup and serves the same purpose. Only difference is that doing a full backup before truncating, you only need to restore the full backup. Doing a tlog backup before truncating, you have to restore the full backup and any tlog backups upto and including the one done before the truncate. That might be a lot of restores. If a person were to do a tlog backup before truncating, I would suggest they do a differential backup, then the tlog backup. Then they only have three backups to restore.
-SQLBill
-
Your point about the differential backup is a very good one as well SQLBill. As for "a lot of restores", yeah in my case yes (every 15 minutes). But using automated scripting, they are usually small files and that those TLOGS are on disk make the task as simple and as fast as your restore scenario (I'm 'old' school). It just go to show that there is more than one way to to achieve a proper solution based on business needs. Have a great day !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
thanks very much for your help everybody!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply