Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with Query

Author  Topic 

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-07-21 : 14:07:08
Hi All,
I use the below stored proc in task scheduler to do a daily full backup of my databases.
I want to send a notification when any of the database backup fails from the script.
I am on sql express,so iam unable to use sql agent notifications.
However i was able to configure mail on sql express.
what code do i need to add to this script to catch the failed backup and send email alert.
below is the script:

CREATE PROCEDURE [dbo].[p_NightlyBackup] (
@DEVICEPATH NVARCHAR(500) = 'C:\SPLLC-Backups\',
@DEVICETYPE NVARCHAR(10) = 'DISK'
)

AS

BEGIN

DECLARE
@DBNAME SYSNAME,
@BACKUPNAME SYSNAME,
@BACKUPDEVICE SYSNAME

DECLARE DBCURSOR CURSOR FOR
SELECT [NAME] FROM SYS.DATABASES
WHERE [NAME] NOT IN ('tempdb','master','model','msdb')
ORDER BY [NAME]

OPEN DBCURSOR
FETCH NEXT FROM DBCURSOR INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @BACKUPDEVICE = @DEVICEPATH + @DBNAME + '.BAK'
SET @BACKUPNAME = RTRIM(@DBNAME) + '-Full Database Backup'

IF EXISTS (SELECT * FROM SYS.BACKUP_DEVICES WHERE [NAME] = @DBNAME )
BEGIN
EXEC master.dbo.sp_dropdevice @logicalname = @DBNAME
EXEC sp_addumpdevice @DEVICETYPE, @DBNAME, @BACKUPDEVICE
END
ELSE
EXEC sp_addumpdevice @DEVICETYPE, @DBNAME, @BACKUPDEVICE

BACKUP DATABASE @DBNAME TO @DBNAME WITH
NOFORMAT,
INIT,
NAME = @BACKUPNAME,
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
FETCH NEXT FROM DBCURSOR INTO @DBNAME
END
CLOSE DBCURSOR
DEALLOCATE DBCURSOR

END


GO

Please help.

Thanks,
Javeed.

mohammad.javeed.ahmed@gmail.com

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-21 : 19:27:47
You can use TRY...CATCH construct.
Explained here: http://msdn.microsoft.com/en-us/library/ms179296(v=sql.105).aspx
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-22 : 02:45:44
see notification example here used within catch

http://www.devtechie.com/post/2011/06/14/TRYCATCH-in-SQL-Server.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-07-22 : 06:38:23
Thanks for the help.
i have modified the proc using try catch.
but when the proc is executed it show mail queued but i dont recieve the email.
however when i execute email block separately i receive the email.
Is there some thing i need to change in the proc.
below is the modified the script.
Alter PROCEDURE [dbo].[p_NightlyBackup] (
@DEVICEPATH NVARCHAR(500) = 'C:\SPLLC-Backups\',
@DEVICETYPE NVARCHAR(10) = 'DISK'
)

AS

BEGIN

DECLARE
@DBNAME SYSNAME,
@BACKUPNAME SYSNAME,
@BACKUPDEVICE SYSNAME,
@SERVERNAME VARCHAR(50),
@SUB VARCHAR(500)

--continueCursor:
SELECT @SERVERNAME=@@SERVERNAME
SET @SUB='[Automated Mail @'+convert(varchar(19),GETDATE(),121)+'] Database backup failed on: '+@@SERVERNAME
BEGIN TRY
BEGIN TRAN
DECLARE DBCURSOR CURSOR FOR
SELECT [NAME] FROM SYS.DATABASES
WHERE [NAME] NOT IN ('tempdb','master','model','msdb')
ORDER BY [NAME]

OPEN DBCURSOR
FETCH NEXT FROM DBCURSOR INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @BACKUPDEVICE = @DEVICEPATH + @DBNAME + '.BAK'
SET @BACKUPNAME = RTRIM(@DBNAME) + '-Full Database Backup'

IF EXISTS (SELECT * FROM SYS.BACKUP_DEVICES WHERE [NAME] = @DBNAME )
BEGIN
EXEC master.dbo.sp_dropdevice @logicalname = @DBNAME
EXEC sp_addumpdevice @DEVICETYPE, @DBNAME, @BACKUPDEVICE
END
ELSE
EXEC sp_addumpdevice @DEVICETYPE, @DBNAME, @BACKUPDEVICE

BACKUP DATABASE @DBNAME TO @DBNAME WITH
NOFORMAT,
INIT,
NAME = @BACKUPNAME,
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
FETCH NEXT FROM DBCURSOR INTO @DBNAME
END
CLOSE DBCURSOR
DEALLOCATE DBCURSOR


END TRY

begin catch
--print 'error recieved'

EXECUTE msdb.dbo.sp_send_dbmail
@recipients='javeed.ahmed@test.com',
@subject = @SUB,
@body = 'Please check the sql server log for backup failure details',
@reply_to = 'javeed.ahmed@test.com'

end catch
COMMIT TRAN
END


GO

mohammad.javeed.ahmed@gmail.com
Go to Top of Page
   

- Advertisement -