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 2008 Forums
 SQL Server Administration (2008)
 Backup transaction log multiple time

Author  Topic 

zero1de
Posting Yak Master

105 Posts

Posted - 2014-10-13 : 08:33:50
Is it possible to backup the transaction logs (log.trn) several times on the day for one DB in that way, that i can add the second buckupfile to the first one (Log.trn). I don't want to lose the logfile for the time between 00-12:00 o'clock.

Example first log backup at 12:00 o'clock and second at 20:00 o'clock. At 20:00 o'clock is the fullbackup .

Is it possible to generate one SQL-Agent Job for both tasks to start at 12:00 o'clock and 20:00 o'clock?

I will schedule SQL Job at 12:00 h and 20:00 o'clock with
this SQL:
BACKUP LOG [XXXX] TO DISK = N'E:\DBBackup\20140924_before_XX_Workshop\XXXX.bak' WITH NOFORMAT, NOINIT, MEDIANAME = N'Log.trn',
NAME = N'XXXX-Transaction Log Backup', NOSKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-13 : 10:27:51
I did not quite understand your backup strategy, but in general, you should take full backups periodically (for example once a day) and transaction log backups more frequently (for example, every hour).

If you have a disaster recovery scenario, to restore the data, you would restore the latest backup and then all the log files that were taken after the full backup.
Go to Top of Page

Shanky
Yak Posting Veteran

84 Posts

Posted - 2014-10-15 : 07:49:56
quote:
Originally posted by zero1de

Is it possible to backup the transaction logs (log.trn) several times on the day for one DB in that way, that i can add the second buckupfile to the first one (Log.trn). I don't want to lose the logfile for the time between 00-12:00 o'clock.

Example first log backup at 12:00 o'clock and second at 20:00 o'clock. At 20:00 o'clock is the fullbackup .

Is it possible to generate one SQL-Agent Job for both tasks to start at 12:00 o'clock and 20:00 o'clock?

I will schedule SQL Job at 12:00 h and 20:00 o'clock with
this SQL:
BACKUP LOG [XXXX] TO DISK = N'E:\DBBackup\20140924_before_XX_Workshop\XXXX.bak' WITH NOFORMAT, NOINIT, MEDIANAME = N'Log.trn',
NAME = N'XXXX-Transaction Log Backup', NOSKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO




Question is not quite clear but I would use my assumption to answer. Are you asking you want to append each log backup to the first on you took if so then by default backup sets are appended unless you use INIT option which overwrites backup set.

Hope this helps

Regards
Shanky
SQL Server MVP
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx
Go to Top of Page
   

- Advertisement -