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.
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
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?