Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Backup LOG, truncate necessary?

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.
Go to Top of Page

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.
Go to Top of Page

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 = 10

But 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 regards
Peter
Go to Top of Page

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
Go to Top of Page

irxn
Starting Member

13 Posts

Posted - 2012-11-09 : 01:26:19
Okay,

thank you.

Your answers solved my problem.ö

Best regards,
Peter
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -