Đang chuẩn bị nút TẢI XUỐNG, xin hãy chờ
Tải xuống
SQL Server Tacklebox- P32: This book, as with almost all books, started out as an idea. I wanted to accumulate together those scripts and tools that I have built over the years so that DBAs could sort through them and perhaps adapt them for their own circumstances. | 6 Monitoring and notifications In order to demonstrate notifications for backup failures I will need to set up a backup job and intentionally make it fail which is something that makes me squeamish even when it s just for demonstration purposes. To keep things simple for this demonstration I m going to use a script that backs up just a single database using the Red Gate extended stored procedure sql_backup. The script is shown in Listing 6.2. DECLARE @exitcode INT DECLARE @sqlErrorCode INT EXECUTE master.sqlbackup N -SQL BACKUP DATABASE DBA_Rep TO DISK D Backups AUTO .sqb WITH COMPRESSION 1 MAILTO_ONERROR rlandrum13@cox.net @exitcode OUTPUT IF @exitCode 0 OR @sqlErrorCode 0 BEGIN -- Raise Error RAISERROR SQL Backup failed 16 1 END Listing 6.2 Red Gate SQL Backup statement. An important point to note about the backup code is use of the Mailto_Onerror parameter. This tells the Red Gate extended stored procedure to use its own native SMTP client to send an email notification if there are any errors with the backup. This is first line of defense for the DBA. If I were to run this code and produce an error I should immediately receive a detailed notification telling me not only what database failed to backup in this case DBA_Rep but also what the cause of the failure was. That information is critical to resolving the issue going forward so that is does not happen again. If instead of using a third party tool I were to use native T-SQL code for example BACKUP DATABASE I would capture any errors in a variable string and then email the failures when the backup job completed using sp_send_dbmail which I have mentioned previously. This type of backup code requires some additional scripting of course. If you are familiar with SSIS it is also possible via a Database Maintenance Plan to add a step in to send mail upon failure. There are really many options available to the DBA to get notifications from failed jobs. OK now to produce the backup failure. This is not actually .
