I'm trying to create a backup plan which creates a full backup twice a day.
A differential backup every hour and a log backup every 5 minutes.
It works fine but I've noticed the log backup (.trn
file) grows and grows in size each time.
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 backup 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 separate 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
(e.g.: LagerDB_06.12.2011_1418.bak
)
But the answer I was really looking for is that I thought it would reset after a full backup. Maybe I should schedule a backup with init
after a full or differential backup?
Best Answer
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:
Save yourself the trouble of hand cranking the necessary scripts and use Ola Hallengren's maintenance solution.