SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ahmeds08
Aged Yak Warrior

India
625 Posts

Posted - 07/21/2013 :  14:07:08  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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

547 Posts

Posted - 07/21/2013 :  19:27:47  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 07/22/2013 :  02:45:44  Show Profile  Reply with Quote
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

India
625 Posts

Posted - 07/22/2013 :  06:38:23  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000