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