December 3, 2014 at 7:12 am
I have a database backup wrapped in a try/catch. It failed but the output in ERROR_MESSAGE is less than helpful.
BACKUP DATABASE is terminating abnormally.
DECLARE @msg VARCHAR(MAX),
@ErrorMessage NVARCHAR(4000);
BEGIN TRY
BACKUP DATABASE [MyDatabase] TO DISK = N'G:\SQLBackup\MyDatabase_Backup.bak'
WITH INIT, NAME = N'MyDatabase Backup', SKIP
END TRY
BEGIN CATCH
SELECT
@ErrorMessage = ERROR_MESSAGE();
SET @msg = 'Backup DB1 Job [usp_BackupDatabases] Failed on MyDatabase - ';
SET @msg += @ErrorMessage;
EXECUTE NOC2.dbo.usp_InsertMessage 43, 101, @msg; -- Insert error message into a table
END CATCH
This is the line inserted into the table:
Backup DB1 Job [usp_BackupDatabases] Failed on MyDatabase - BACKUP DATABASE is terminating abnormally.
So what do I need to get a more explanatory error message i.e. why did the backup fail?
December 3, 2014 at 7:39 am
There should be some more info in the SQL error log, I would asume something similar to this:
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'G:\SQLBackup\MyDatabase_Backup.bak'. Operating system error x(failed to retrieve text for this error. Reason: xxxxx).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
If thats the case check if 'G:\SQLBackup\' is accessible and make sure that account have write premissions
December 3, 2014 at 7:51 am
As I bang my head on the wall. Duh!
12/02/2014 21:15:02,Backup,Unknown,Error: 3041<c/> Severity: 16<c/> State: 1.
12/02/2014 21:15:02,spid52,Unknown,BackupDiskFile::CreateMedia: Backup device 'G:\SQLBackup\MyDatabase_Backup.bak' failed to create. Operating system error 32(The process cannot access the file because it is being used by another process.).
12/02/2014 21:15:02,spid52,Unknown,Error: 18204<c/> Severity: 16<c/> State: 1.
So I have to find why the file was being used.
Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply