December 18, 2017 at 5:11 am
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='DBA',
@recipients ='arvindinfa2014@gmail.com',
@subject = 'Differential Backup Report',
@execute_query_database = 'master',
@query='EXECUTE [dbo].[DatabaseBackup_Differential]',
@body_format='html',
@body='Differential Backup of USER Databases taken successfully on Server1',
@exclude_query_output=1,
@append_query_error=1,
@attach_query_result_as_file = 1
While Executing this has query i am getting mail with the successful backup, but while running has agent job it just showing run has "Successful" , no backup taken, please let me know the solution for this problem
thanks in advance
December 18, 2017 at 5:17 am
What database context is the SQL job running in? And what database context is the DatabaseBackup_Differential procedure in?
December 18, 2017 at 5:20 am
servicename startup_type_desc status_desc last_startup_time service_account is_clustered cluster_nodename filename startup_type status process_id
SQL Server Agent (MSSQLSERVER) Automatic Running NULL NT Service\SQLSERVERAGENT N NULL "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\SQLAGENT.EXE" -i MSSQLSERVER 2 4 8924
Tried giving this permission also :GRANT EXECUTE ON [xp_sysmail_format_query] TO [NT Service\SQLSERVERAGENT]
Procedure resides in master db
December 18, 2017 at 5:21 am
All you've told us is that you have a job that sends an e-mail saying that a backup succeeded. Does the job actually do the backup as well? Please post the whole job definition.
As an aside, I would advise you not to post your e-mail address on a public forum.
Edit - sorry, I didn't notice that the sp_send_dbmail command executes the backup stored procedure as well. It might be useful if you posted the definition of that stored procedure. Have you tested to see what happens when the stored procedure fails for any reason - is an e-mail still sent saying that the backup succeeded?
John
December 18, 2017 at 5:34 am
GA_SQL - Monday, December 18, 2017 5:20 AMProcedure resides in master db
And what about the job, what database context is that set to run as?
As a side note, why bother wrapping the backup in a DB mail command, execute the backup procedure on its own and use operators and the inbuilt alerting methods of the jobs to alert on completion of the job
December 18, 2017 at 5:40 am
Backup Script:
USE [master]
GO
/****** Object: StoredProcedure [dbo].[DatabaseBackup_Differential] Script Date: 12/18/2017 6:36:08 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DatabaseBackup_Differential]
AS
BEGIN
-- add the folder as per the path
DECLARE @Baksql VARCHAR(8000)
DECLARE @BackupFolder VARCHAR(100)
DECLARE @BackupFile VARCHAR(100)
DECLARE @BAK_PATH VARCHAR(4000)
DEclare @BackupDate varchar(100)
-- Setting value of backup date and folder of the backup
SET @BackupDate = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),'-',''),':',''),' ','_')
SET @BackupFolder = 'G:\Backup\Differential\'
-- Declaring cursor
DECLARE c_bakup CURSOR FAST_FORWARD READ_ONLY FOR
SELECT NAME FROM SYS.DATABASES
WHERE state_desc = 'ONLINE' -- Consider databases which are online
AND database_id > 4 -- Exluding system databases
-- Opening and fetching next values from sursor
OPEN c_bakup
FETCH NEXT FROM c_bakup INTO @BackupFile
WHILE @@FETCH_STATUS = 0
BEGIN
SET @BAK_PATH = @BackupFolder + @BackupFile
-- Creating dynamic script for every databases backup
SET @Baksql = 'BACKUP DATABASE ['+@BackupFile+'] TO DISK = '''+@BAK_PATH+'_DiffBackup_'+@BackupDate+'.bak'' WITH DIFFERENTIAL;'
-- Executing dynamic query
PRINT (@Baksql)
EXEC(@Baksql)
-- Opening and fetching next values from sursor
FETCH NEXT FROM c_bakup INTO @BackupFile
END
-- Closing and Deallocating cursor
CLOSE c_bakup
DEALLOCATE c_bakup
END
GO
this is the SP called in the job
while running has job it shows only the success message, no backup taken, no email sent
Job definition:
USE [msdb]
GO
/****** Object: Job [DatabaseBackup_Differential_Test] Script Date: 12/18/2017 6:38:51 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 12/18/2017 6:38:51 AM ******/
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'DatabaseBackup_Differential_Test',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@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'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Step 1] Script Date: 12/18/2017 6:38:51 AM ******/
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'EXEC msdb.dbo.sp_send_dbmail
@profile_name =''DBA'',
@recipients =''email@email.com'',
@subject = ''Differential Backup Report'',
--@execute_query_database = ''master'',
@query=''EXECUTE [dbo].[DatabaseBackup_Differential]'',
@body_format=''html'',
@body=''Differential Backup of USER Databases taken successfully on Server1'',
@exclude_query_output=1,
@append_query_error=1,
@attach_query_result_as_file = 1
',
@database_name=N'master',
@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'Diff',
@enabled=1,
@freq_type=8,
@freq_interval=118,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20170705,
@active_end_date=99991231,
@active_start_time=20000,
@active_end_time=235959,
@schedule_uid=N'95f532cc-6d0b-45cb-a0e7-9676b462dbcf'
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
Note: Sir, i wont use email id in the forums, sorry and thanks
December 18, 2017 at 5:48 am
GA_SQL - Monday, December 18, 2017 5:40 AMwhile running has job it shows only the success message, no backup taken, no email sent
I'm guessing the backup failed, then. Have you checked the backupset table in msdb to see whether a backup was made? What happens if you call the stored procedure directly?
John
December 18, 2017 at 6:00 am
While calling SP directly it executes fine, calling from agent job it is not taking the backup and no confirmation mail sent..
checked with msdb.dbo.backupset it shows only the backup taken via SP directly
December 18, 2017 at 6:04 am
GA_SQL - Monday, December 18, 2017 6:00 AMWhile calling SP directly it executes fine, calling from agent job it is not taking the backup and no confirmation mail sent..checked with msdb.dbo.backupset it shows only the backup taken via SP directly
Have you tried 3 part naming the procedure? EXEC [master].[dbo].[DatabaseBackup_Differential]?
This seems an overly complicated way to get an email alert upon the job running. My advise would be to look at operators and job alerts and use that method instead. So all the job would do is execute the DatabaseBackup_Differential backup only no mail no fancy business, then its up to the completion of the job to then fire the email.
December 18, 2017 at 6:09 am
@query=''EXECUTE [dbo].[DatabaseBackup_Differential]'' i used this without master. but still not executing, i used only 2 parts
December 18, 2017 at 6:13 am
GA_SQL - Monday, December 18, 2017 6:09 AM@query=''EXECUTE [dbo].[DatabaseBackup_Differential]'' i used this without master. but still not executing, i used only 2 parts
Probably due to the fact that sp_send_dbmail isn't executing in the master database, as its an external call it has probably executed in msdb as that is where sp_send_dbmail lives.
Execute using 3 part name or specify the query database parameter to sp_send_dbmail.
Again though, this wouldn't be my approach, why write your own alerting method for jobs when SQL has had one since as long as I can remember with SQL 2000 which is tried and tested and works a charm.
December 18, 2017 at 6:25 am
In another server (SQL Server 2012) i am running the same script , it works fine ,please help in resolving the issue
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='DBA',
@recipients ='Email@email.com',
@subject = 'Differential Backup Report',
@query='EXECUTE [dbo].[DatabaseBackup_Differential]',
@body='Differential Backup Taken Successfully on Server2'
December 18, 2017 at 6:30 am
GA_SQL - Monday, December 18, 2017 6:25 AMIn another server (SQL Server 2012) i am running the same script , it works fine ,please help in resolving the issue
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='DBA',
@recipients ='Email@email.com',
@subject = 'Differential Backup Report',
@query='EXECUTE [dbo].[DatabaseBackup_Differential]',
@body='Differential Backup Taken Successfully on Server2'
Without any direct error message we cannot help. We cannot see you servers so we cannot help investigate what exactly is occurring.
From the code you have provided it all looks ok apart from maybe the 3 part naming issue.
If the job is running fine on server 2 but not server 1, then look at what is different between them.
December 18, 2017 at 6:33 am
Sir, No error is coming, just showing the Success messages....
December 18, 2017 at 6:35 am
GA_SQL - Monday, December 18, 2017 6:33 AMSir, No error is coming, just showing the Success messages....
Then as detailed if it works on Server2 and not Server1 go and look what is different between them.
Something must be different if they are running the same code.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply