0

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

2
  • Are you sure the command works? I can't test it at the moment but I'd be suspicious of any backup that completes in 00:00:00. Apr 6 '18 at 4:31
  • "cannot see differential backup in the media" - what do you mean here? You've shown your job (we only needed the step command btw) but not how you're looking at the media. Is there a reason you append to a single file all month long, rather than just keep individual files with timestamps? Apr 6 '18 at 12:29
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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