SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Growing Log Files


Growing Log Files

Author
Message
GilaMonster
GilaMonster
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220083 Visits: 46279
mp5387 (4/8/2013)
Now for providing transactional backup which method is good? append or overwrite


Neither.

Overwrite would be rather silly with log backups since you need all log backups in sequence to restore. Set it to overwrite and you'll only have the last one, useless for restoring.
Append leaves all backups in the same file which means your file is growing larger and larger and anything goes wrong you lose the entire set of backups rather than one.

All backups to their own file, timestamp in the file name.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


mp5387
mp5387
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 90
Thanks for your prompt answer:-)
SQLisAwE5OmE
SQLisAwE5OmE
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3000 Visits: 3075
GilaMonster (4/8/2013)
mp5387 (4/8/2013)
Now for providing transactional backup which method is good? append or overwrite


Neither.

Overwrite would be rather silly with log backups since you need all log backups in sequence to restore. Set it to overwrite and you'll only have the last one, useless for restoring.
Append leaves all backups in the same file which means your file is growing larger and larger and anything goes wrong you lose the entire set of backups rather than one.

All backups to their own file, timestamp in the file name.


Also, create a clean up job to remove the old trans log backup files after taking a new full backup everyday.

SueTons.

Regards,
SQLisAwe5oMe.
SQLisAwE5OmE
SQLisAwE5OmE
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3000 Visits: 3075
Please ignore.....sent in error.

SueTons.

Regards,
SQLisAwe5oMe.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220083 Visits: 46279
SQLCrazyCertified (4/8/2013)
GilaMonster (4/8/2013)
mp5387 (4/8/2013)
Now for providing transactional backup which method is good? append or overwrite


Neither.

Overwrite would be rather silly with log backups since you need all log backups in sequence to restore. Set it to overwrite and you'll only have the last one, useless for restoring.
Append leaves all backups in the same file which means your file is growing larger and larger and anything goes wrong you lose the entire set of backups rather than one.

All backups to their own file, timestamp in the file name.


Also, create a clean up job to remove the old trans log backup files after taking a new full backup everyday.

SueTons.


I wouldn't recommend that. In fact, I'd say that's not a particularly good idea. What happens if that new full backup is damaged and won't restore? By deleting all the log backups from the previous day, you now have no way to recover.

I keep a full week of full and log backups if I can. At minimum 2 days worth.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQLisAwE5OmE
SQLisAwE5OmE
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3000 Visits: 3075
GilaMonster (4/8/2013)
SQLCrazyCertified (4/8/2013)
GilaMonster (4/8/2013)
mp5387 (4/8/2013)
Now for providing transactional backup which method is good? append or overwrite


Neither.

Overwrite would be rather silly with log backups since you need all log backups in sequence to restore. Set it to overwrite and you'll only have the last one, useless for restoring.
Append leaves all backups in the same file which means your file is growing larger and larger and anything goes wrong you lose the entire set of backups rather than one.

All backups to their own file, timestamp in the file name.


Also, create a clean up job to remove the old trans log backup files after taking a new full backup everyday.


Agreed and thanks for correcting!

SueTons.
SueTons.


I wouldn't recommend that. In fact, I'd say that's not a particularly good idea. What happens if that new full backup is damaged and won't restore? By deleting all the log backups from the previous day, you now have no way to recover.

I keep a full week of full and log backups if I can. At minimum 2 days worth.


Regards,
SQLisAwe5oMe.
mp5387
mp5387
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 90
My plan for backup is daily full backup, and transactional log backup for every 5 minutes because we do not have much transactions.My data base recovery model is "Full", and I scheduled transactional Backup with time stamp file name, and it works. Now I have problem with restoring transactional log backup. I restored my Full backup with Recovery state " RECOVERY WITH STANDBY" , and Restore options "WITH REPLACE", and it worked for full database, but when I want to restore transactional log backup, it is grayed out. Do you have any solution for this problem?
Thank you in advance for any help you can provide.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search