Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

My question might sound rather strange but I'm not really a SQL expert and I can't find the answer..

I'm trying to create a backup plan which creates a full backup twice a day. A differential back-up every hour and a log backup every 5 minutes.

It works fine but I've noticed the log backup (.trn) grows and grows in size each time. contents log backup

If I use backup log with init it overwrites and I'm getting the too recent error: I thought that after another full or differential back-up the next log backup would only include the log back-ups 'since the last full / diff backup'?

BACKUP DATABASE 
    lagerdb 
TO DISK = N'C:\SQLBACKUP\lagerdb-rechts.bak' 
WITH INIT, NOUNLOAD, NAME = N'lagerdb full', NOSKIP, STATS = 20, NOFORMAT

BACKUP DATABASE 
    lagerdb TO DISK = N'c:\SQLBACKUP\lagerdbd-rechts.bak' 
WITH INIT, NOUNLOAD, NAME = N'lagerdb differential', NOSKIP, STATS = 20, NOFORMAT, DIFFERENTIAL 

BACKUP LOG 
    lagerdb 
TO DISK = N'C:\SQLBACKUP\lagerdb-rechts.trn' 
WITH NAME = N'lagerdb logs'

I did something with the suggestion below to backup the log to seperate files with a datetime stamp:

 -- Declares a string
 declare @sql nvarchar(255)
 declare @ParmDefinition NVARCHAR(500)

 -- declares the path name. Seperated by disk drive and directory.
 declare @bkName varchar(255)

 -- Declares the filename. 
 declare @bkdbName varchar(50)
 declare @bkdbDateTime varchar(20)

 --Get Timestring
 set @sql = N'select @parm1OUT = CONVERT(varchar(20),CONVERT(DATE,GETDATE(),104),104)     + ''_'' + Substring(CONVERT(varchar(8),getdate(),114),1,2) + Substring(CONVERT(varchar(8),getdate(),114),4,2)'
 SET @ParmDefinition=N'@parm1OUT varchar(20) OUTPUT'

 exec sp_executesql 
 @sql, 
 @ParmDefinition,
 @parm1OUT=@bkdbDateTime OUTPUT

 set @bkdbName = 'LagerDB_' + @bkdbDateTime

 -- Enter the Drive letter below. Please remove C: as current value.
 set @bkName = 'C:'

 -- Enter the folder name below (with lead and end '\' back slashes.
 -- Ensure that you change the folder from Temp below.
 set @bkName = @bkName + '\SQLBackup\'
 set @bkName = @bkName + @bkdbName 

 -- Sets the backup running by executing the @sql string.
 set @sql = 'BACKUP DATABASE lagerdb TO DISK = '''+@bkName+'.bak'''

 print @sql
 exec sp_executesql @sql

(EG: LagerDB_06.12.2011_1418.bak)

But the answer I was really looking for is that I thought it would reset after a full back-up. Maybe I should schedule a backup with init after a full or differential backup?

share|improve this question

migrated from stackoverflow.com Dec 6 '11 at 10:32

This question came from our site for professional and enthusiast programmers.

1 Answer 1

You are backing up the log to the same file repeatedly. Subsequent backups are being appended to that file, which is what you can see in your screen capture. Typically, we tend to backup to dated files:

BACKUP LOG 
    lagerdb 
TO DISK = N'C:\SQLBACKUP\lagerdb-rechts_201112061130.trn' 
WITH NAME = N'lagerdb logs'

Save yourself the trouble of hand cranking the necessary scripts and use Ola Hallengren's maintenance solution.

share|improve this answer
    
Great link Mark! –  Mike Dole Dec 6 '11 at 13:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.