August 9, 2016 at 5:06 am
Our environment is a little weird. Instead of just using a maintenance job or backing up dbs to one root folder, we have to create a structure mimicking the de-dup structure our corporate office uses. What that means is we have a structure like this:
NASName
DB1
20160801_FULL
20160802
20160803
...
20160807_FULL
DB2
20160801_FULL
20160802
20160803
...
20160807_FULL
The folders with _FULL have the full backup of the week. The folders without the _FULL have the differentials. Transaction logs are in both folders. The individual folders are named after the date the backup is being taken on.
There are two / three jobs on each server. The first job runs in the morning to create the folders. The second job does the FULL/DIFFERENTIAL backups. The third job creates transaction log backups for those databases in FULL recovery mode on a couple of servers, but not all servers.
The job in question is the folder job. It works great for creating the folder structure exactly as we need it. The problem I'm running into is how to use a TRY...CATCH to send us email notifications when the job fails. I deliberately set up a failure scenario in the code. The job failed, sent a failure notification, but did not hit the CATCH block to send the email with the error.
I haven't really used TRY...CATCH a lot. Could someone look at the below code and let me know what I'm missing? I plan to apply the same logic to the backup jobs as soon as I have it corrected in the folder jobs.
BEGIN TRY
---Create backup folders for the day.
--SET QUOTED_IDENTIFIER OFF;
IF (SELECT OBJECT_ID('tempdb..#DBResultSet')) IS NOT NULL
DROP TABLE #DBResultSet;
IF (SELECT OBJECT_ID('tempdb..#BackupResultSet')) IS NOT NULL
DROP TABLE #BackupResultSet;
--DROP temp tables if they exist
CREATE TABLE #DBResultSet (Directory VARCHAR(400), Folder VARCHAR(50));
CREATE TABLE #BackupResultSet (DailyFolder VARCHAR(50));
--Create temp tables
DECLARE @BaseFolder VARCHAR(128),@DBMainBackupFolder VARCHAR(200),@DatabaseDailyFolder VARCHAR(256),@DbName SYSNAME, @TodayDate CHAR(8), @FullOrDiff BIT,
@MySQL VARCHAR(500);
--Set up variables. BackupFolder is the
-- Input parameters needed FROM the user.
SET @BaseFolder = '\\Myas\backup\share\';
SELECT @TodayDate = CONVERT(CHAR(8),GETDATE(),112);
SELECT @FullOrDiff = CASE WHEN DATEPART(dw,@TodayDate) = 3 THEN 1 ELSE 0 END;
INSERT INTO #DBResultSet (Folder)
EXEC master.sys.xp_subdirs @BaseFolder;
DECLARE Db CURSOR FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('tempdb') AND name NOT LIKE 'Report_%'
AND name NOT LIKE '%ProdCopy'
AND state_desc = 'ONLINE' AND replica_id IS NULL AND source_database_ID IS NULL;
OPEN Db;
FETCH NEXT FROM Db INTO @DbName;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @DBMainBackupFolder = @BaseFolder + '\' + @DbName ;
-- Dynamically create a base folder for each database.
SELECT @DatabaseDailyFolder = CASE @FullOrDiff WHEN 1 THEN @BaseFolder + '\' + @DbName + '\' + @TodayDate + '_FULL' + '\'
WHEN 0 THEN @BaseFolder + '\' + @DbName + '\' + @TodayDate + '\'
END;
-- Dynamically create the day's backup folder for each database.
SET @MySQL = 'INSERT INTO #BackupResultSet (DailyFolder) EXEC master.dbo.xp_subdirs '' + @DBMainBackupFolder + '';';
--Get list of existing daily folders
EXEC (@MySQL);
IF (SELECT directory FROM #DBResultSet WHERE directory = @DBMainBackupFolder) IS NULL
EXEC master.sys.xp_create_subdir @DBMainBackupFolder;
--Check for main DB folder
IF (SELECT DailyFolder FROM #BackupResultSet WHERE DailyFolder = @DatabaseDailyFolder AND DbName = @DBName) IS NULL
EXEC master.sys.xp_create_subdir @DatabaseDailyFolder; --Error introduced in line above.
--Check for daily bakup folder
--SELECT @DBName AS DBName, @DBMainBackupFolder AS BackupDirectory, @DatabaseDailyFolder AS DBFolder, @FullOrDiff AS FullOrDiff, @MySQL AS SQLCommand; /* For troubleshooting */
--SELECT * FROM #DBResultSet; /* For troubleshooting */
--SELECT * FROM #BackupResultSet; /* For troubleshooting */
TRUNCATE TABLE #BackupResultSet;
FETCH NEXT FROM Db INTO @DbName;
END
-- Clean up
CLOSE Db;
DEALLOCATE Db;
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT, @ErrorSubject VARCHAR(50);
SET @ErrorSubject = 'FAILURE: DBA_CreateBackupFolder Job on ' + @@SERVERNAME
SET @ErrorMessage = 'Dear Admin,
Due to some error, the DBA_CreateBackupFolder job on Server ' + @@Servername +
' has failed.
'
+ 'Given below is the error message, state and severity.
ErrorMessage : ' + ERROR_MESSAGE()
+ '
ErrorState : ' + CONVERT(VARCHAR(7),@ErrorState)
+ '
ErrorSeverity : ' + CONVERT(VARCHAR(7),@ErrorSeverity)
ROLLBACK TRANSACTION;
EXEC msdb.dbo.sp_send_dbmail @profile_name='MyProfile@email.com',
@recipients='DBATeam@email.com',
@subject=@ErrorSubject,
@body= @ErrorMessage,
@body_format = 'Text';
END CATCH;
GO
August 9, 2016 at 5:40 am
Wow, those double quotes are really throwing the SQL parser off, makes it difficult to read.
It's probably obvious, but I didn't see it. Which line of code is (deliberately) creating the error and what is the error?
August 9, 2016 at 6:20 am
Phil Parkin (8/9/2016)
Wow, those double quotes are really throwing the SQL parser off, makes it difficult to read.It's probably obvious, but I didn't see it. Which line of code is (deliberately) creating the error and what is the error?
Whoops. Double quotes removed for readability.
The error wasn't in the code I originally posted because I grabbed the wrong query window. I've put it back into the second temp table SELECT where it's about to execute the subdir command. Also commented it.
August 9, 2016 at 7:18 am
So it's this row that generates the error?
EXEC master.sys.xp_create_subdir @DatabaseDailyFolder;
If so, I think you might have to modify your code to capture the execution status of the xp and then, based on the status, throw the appropriate error.
August 11, 2016 at 4:35 am
Phil Parkin (8/9/2016)
So it's this row that generates the error?
EXEC master.sys.xp_create_subdir @DatabaseDailyFolder;If so, I think you might have to modify your code to capture the execution status of the xp and then, based on the status, throw the appropriate error.
Actually, it should be failing on the IF statement right above it.
IF (SELECT DailyFolder FROM #BackupResultSet WHERE DailyFolder = @DatabaseDailyFolder AND DbName = @DBName) IS NULL
EXEC master.sys.xp_create_subdir @DatabaseDailyFolder; --Error introduced in line above.
--Check for daily bakup folder
DbName doesn't exist as a column in #BackupResultSet. And it calls that out in the message results I'm getting. But maybe SQL is only parsing that problem when it executes the stored proc... I'll take a look at that.
August 11, 2016 at 5:58 am
Brandie Tarvin (8/11/2016)
Phil Parkin (8/9/2016)
So it's this row that generates the error?
EXEC master.sys.xp_create_subdir @DatabaseDailyFolder;If so, I think you might have to modify your code to capture the execution status of the xp and then, based on the status, throw the appropriate error.
Actually, it should be failing on the IF statement right above it.
IF (SELECT DailyFolder FROM #BackupResultSet WHERE DailyFolder = @DatabaseDailyFolder AND DbName = @DBName) IS NULL
EXEC master.sys.xp_create_subdir @DatabaseDailyFolder; --Error introduced in line above.
--Check for daily bakup folder
DbName doesn't exist as a column in #BackupResultSet. And it calls that out in the message results I'm getting. But maybe SQL is only parsing that problem when it executes the stored proc... I'll take a look at that.
OK, you are right ... the invalid column name error is thrown before the statement is even executed. So it's not a run-time error as such and that's why the TRY CATCH block does not capture it.
If you have Intellisense enabled and set to underline errors, DbName should be underlined for you at design time.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply