Shouldn't the trans log backup be truncating my log file?

Question

Shouldn't the trans log backup be truncating my log file?

Viewing 4 posts - 31 through 33 (of 33 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736252 More actions · Answer 1

I never append. You are risking your databases in one file, and if something happens to any part of that file, you could be completely without backups. I always use a new file.

A second vote for Ola's scripts.

TomThomson SSC Guru Points: 104773 More actions · Answer 2

SkyBox (9/20/2010)
I considered using the Maint plan wizard, but couldn't decide if I should use the append vs. overwrite option. What am I missing here? I did notice the option to set the number of days to retain backups.
Thanks

Trouble with overwrite is that you lose the one you are overwriting. Trouble with append is that if something goes wrong you may lose all the backups in the file. So the answer is never do either, always create a new file (you can overwrite instead of deleting, of course, as you weren't going to keep it if you deleted it, but it's cleaner to delete - that way you never get into a situation where you think you've overwritten but you haven't, you've got an old backup not the latest).

Tom

SkyBox SSCarpal Tunnel Points: 4288 More actions · Answer 3

Tom.Thomson (9/21/2010)
SkyBox (9/20/2010)
I considered using the Maint plan wizard, but couldn't decide if I should use the append vs. overwrite option. What am I missing here? I did notice the option to set the number of days to retain backups.
Thanks
Trouble with overwrite is that you lose the one you are overwriting. Trouble with append is that if something goes wrong you may lose all the backups in the file. So the answer is never do either, always create a new file (you can overwrite instead of deleting, of course, as you weren't going to keep it if you deleted it, but it's cleaner to delete - that way you never get into a situation where you think you've overwritten but you haven't, you've got an old backup not the latest).

Sounds like Ola's scripts may be the way to go. Thanks all!

subbu_e SSCrazy Points: 2431 More actions · Answer 4

if you are changing into simple recovery model and truncate the log , it will break the LSN ,if you have T-Log Scheulde you will not be having the proper LSN, incase you want restore with your old T-Log will not be able to restore them.

when ever you are truncating a log using simple recovery you need to intiate the FULL backup again

Regards,

Subbu

http://mssqlforum.wordpress.com

Regards,
Subbu
Click here to Get Speedy answer or solution