Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Growing Log Files Expand / Collapse
Author
Message
Posted Monday, April 8, 2013 11:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:53 AM
Points: 42,822, Visits: 35,952
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 2008, MVP
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

Post #1439961
Posted Monday, April 8, 2013 12:53 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 2:49 PM
Points: 18, Visits: 80
Thanks for your prompt answer
Post #1439984
Posted Monday, April 8, 2013 12:59 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 11:04 PM
Points: 404, Visits: 2,483
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.
Post #1439989
Posted Monday, April 8, 2013 1:01 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 11:04 PM
Points: 404, Visits: 2,483
Please ignore.....sent in error.

SueTons.


Regards,
SQLisAwe5oMe.
Post #1439990
Posted Monday, April 8, 2013 1:07 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:53 AM
Points: 42,822, Visits: 35,952
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 2008, MVP
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

Post #1439992
Posted Monday, April 8, 2013 1:20 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 11:04 PM
Points: 404, Visits: 2,483
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.
Post #1439995
Posted Friday, April 12, 2013 5:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 2:49 PM
Points: 18, Visits: 80
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.
Post #1441976
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse