| Author |
Topic |
|
irxn
Starting Member
13 Posts |
 Posted - 2012-11-08 : 09:17:04
|
| Hi,On our server we actual have following Backup strategy (Recovery Model Full):Daily Full Backup, Daily Backup Log with truncate only, Hourly Diff Backup.I want to change this to:Weekly Full Backup, Daily Diff Backup, Hourly Tranaction Log Backup.My question is: Do i have to Backup Log with truncate only when I do a hourly transaction log backup?Or is the log truncated when i backup the log?Best regards,Peter |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-11-08 : 09:59:07
|
The log is truncated when the log backup is finished.Don't do the "truncate only" thing.
Too old to Rock'n'Roll too young to die. |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-11-08 : 10:01:13
|
For the possibility to restore to the point of "there was a big problem"... you should go for a log backup every 15 minutes or so.
Too old to Rock'n'Roll too young to die. |
|
|
|
irxn
Starting Member
13 Posts |
Posted - 2012-11-08 : 10:13:08
|
| Hi,thanks for your answers. 15 Minutes is also possible.I have an additional question:How should I store the log backup to disk?I have used Server Management Studio to create log backup task. But I'm not sure how to save the log backups.This is the script generated:BACKUP LOG [backup_test] TO DISK = N'E:\BackupTEst\test_log.trn' WITH NOFORMAT, NOINIT, NAME = N'backup_test_backup_20121108161106', SKIP, REWIND, NOUNLOAD, STATS = 10But so the whole backups are in this file. Can i delete old backups from this file? For example Backups older than 2 days? Or do I have to make a own file for every backup every 15 Minutes?Best regardsPeter |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2012-11-08 : 12:00:24
|
| Setup a maintenance plan to do the transaction log backups.It will generate a new log file every time it runs, and take care of deleting the old files after the number of days you specify.CODO ERGO SUM |
|
|
|
irxn
Starting Member
13 Posts |
Posted - 2012-11-09 : 01:26:19
|
| Okay, thank you.Your answers solved my problem.öBest regards,Peter |
|
|
|
irxn
Starting Member
13 Posts |
Posted - 2012-11-13 : 05:55:27
|
| Hi, I have one additional question to my problem:When the full backup is running, the backup tasks takes 3 hours. During this time, is there a problem when I do my log backups every 15 minutes? Or should the log backup be disabled during the time of the full backup?Is there a problem when restoring the Full and log backups?Best regards,Peter |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-11-13 : 14:30:23
|
| There is no problem running transaction log backups during a full or differential backup. You want those log backups - just in case you have an issue during the backup. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-11-13 : 14:41:31
|
quote:
Originally posted by webfred
For the possibility to restore to the point of "there was a big problem"... you should go for a log backup every 15 minutes or so.
Too old to Rock'n'Roll too young to die.
Why? This is not necessarily true. Log backup frequency depends on activity, not PIT recovery, you can still recover to a point in time with no log backups (As long as you can take a tail of the log backup).You cannot make generalized recommendations like that without knowing a lot more about the system.-Chad |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-11-13 : 14:42:39
|
quote:
Originally posted by irxn
Hi, I have one additional question to my problem:When the full backup is running, the backup tasks takes 3 hours. During this time, is there a problem when I do my log backups every 15 minutes? Or should the log backup be disabled during the time of the full backup?Is there a problem when restoring the Full and log backups?Best regards,Peter
The log won't clear until the Full backup completes, other than that, no problem.-Chad |
|
|
|
irxn
Starting Member
13 Posts |
Posted - 2012-11-14 : 02:16:10
|
| Hi,thanks for your answers. Now I can change our backup strategy.Best Regards,Peter |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-11-14 : 02:52:18
|
quote:
Originally posted by chadmat
quote:
Originally posted by webfred
For the possibility to restore to the point of "there was a big problem"... you should go for a log backup every 15 minutes or so.
Too old to Rock'n'Roll too young to die.
Why? This is not necessarily true. Log backup frequency depends on activity, not PIT recovery, you can still recover to a point in time with no log backups (As long as you can take a tail of the log backup).You cannot make generalized recommendations like that without knowing a lot more about the system.-Chad
You are right and I am sorry for that.I will try to avoid that kind of posts but can't promise because I am human 
Too old to Rock'n'Roll too young to die. |
|
|
|
|
Backup LOG, truncate necessary?