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.
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')ASBEGINDECLARE @DBNAME SYSNAME, @BACKUPNAME SYSNAME, @BACKUPDEVICE SYSNAMEDECLARE 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 ENDCLOSE DBCURSORDEALLOCATE DBCURSORENDGOPlease 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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
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')ASBEGINDECLARE @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 TRANDECLARE 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 ENDCLOSE DBCURSORDEALLOCATE DBCURSOREND TRYbegin 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 catchCOMMIT TRANENDGOmohammad.javeed.ahmed@gmail.com |
|
|
|
|
|
|
|