Sql-server – Backups jobs are successful but cannot see differential backup in the media

sql server

I have a backup job running on my Sql server that takes a complete backup at start of month and diff every two hour between 9 to 5.Now the problem backup jobs are running and successful but I cannot see differential backup in the media.

Backup job
USE [msdb]
GO

/****** Object:  Job [Backup]    Script Date: 04/06/2018 12:31:21 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 04/06/2018 12:31:21 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Backup', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=2, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'Server\user', 
        @notify_email_operator_name=N'AdminOperator', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Step-1]    Script Date: 04/06/2018 12:31:21 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step-1', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'DECLARE @File varchar(1000)
DECLARE @i int
declare @m as varchar(2)
declare @d as varchar(2)
declare @dt as varchar(8)
DECLARE @strSQL nvarchar(1000)
--if (day(getdate())<10)
--  set @d = ''0'' + cast(day(getdate()) as varchar(2)) 
--else
--  set @d =  cast(day(getdate()) as varchar(2)) 

if (month(getdate())<10)
    set @m = ''0'' + cast(month(getdate()) as varchar(2)) 
else
    set @m =  cast(month(getdate()) as varchar(2)) 
Set @dt = cast(year(getdate()) as varchar(4)) +  @m


SET @File = ''D:\SQLBackups\SQLBackups_database\database'' + @dt + ''.BAK''

EXEC master..xp_fileexist @File, @i out
 IF @i = 1 BEGIN     --FILE EXISTS
    set @strSQL = ''BACKUP DATABASE database  TO  DISK = N'''''' + @File + ''''''  WITH  DIFFERENTIAL''  
 END
 ELSE BEGIN --FILE NOT EXISTS
    set @strSQL = ''BACKUP DATABASE database  TO  DISK = N'''''' + @File + ''''''  WITH  INIT''  
 END


EXEC sp_executesql @strSQL


', 
        @database_name=N'database', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule-1', 
        @enabled=1, 
        @freq_type=8, 
        @freq_interval=62, 
        @freq_subday_type=8, 
        @freq_subday_interval=2, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=1, 
        @active_start_date=20100823, 
        @active_end_date=99991231, 
        @active_start_time=90000, 
        @active_end_time=190000, 
        @schedule_uid=N'50451ca4-7f63-45ce'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Log:
enter image description here

Best Answer

This part of code clearly states that you are appending the diff backup

Set @dt = cast(year(getdate()) as varchar(4)) + @m SET @File = ''D:\SQLBackups\SQLBackups_database\database'' + @dt + ''.BAK'' EXEC master..xp_fileexist @File, @i out IF @i = 1 BEGIN --FILE EXISTS set @strSQL = ''BACKUP DATABASE database TO DISK = N'''''' + @File + '''''' WITH DIFFERENTIAL'' END ELSE BEGIN --FILE NOT EXISTS set @strSQL = ''BACKUP DATABASE database TO DISK = N'''''' + @File + '''''' WITH INIT'' END

Instead of carving out your own solution which clearly is not smart e.g. exclude offline db, take log backups etc is not good .. use Ola's backup solution.

Also, make sure you test your restore plan by actually restoring the dbs and trying a point-in-time recovery where possible.