SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Backup LOG, truncate necessary?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

irxn
Starting Member

13 Posts

Posted - 11/08/2012 :  09:17:04  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 11/08/2012 :  09:59:07  Show Profile  Visit webfred's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 11/08/2012 :  10:01:13  Show Profile  Visit webfred's Homepage  Reply with Quote
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 - 11/08/2012 :  10:13:08  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 11/08/2012 :  12:00:24  Show Profile  Reply with Quote
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 - 11/09/2012 :  01:26:19  Show Profile  Reply with Quote
Okay,

thank you.

Your answers solved my problem.รถ

Best regards,
Peter
Go to Top of Page

irxn
Starting Member

13 Posts

Posted - 11/13/2012 :  05:55:27  Show Profile  Reply with Quote
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

USA
796 Posts

Posted - 11/13/2012 :  14:30:23  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 11/13/2012 :  14:41:31  Show Profile  Visit chadmat's Homepage  Reply with Quote
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

USA
1974 Posts

Posted - 11/13/2012 :  14:42:39  Show Profile  Visit chadmat's Homepage  Reply with Quote
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 - 11/14/2012 :  02:16:10  Show Profile  Reply with Quote
Hi,

thanks for your answers. Now I can change our backup strategy.

Best Regards,
Peter
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 11/14/2012 :  02:52:18  Show Profile  Visit webfred's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000