When BACKUP DATABASE
generates an error, it actually generates two. Unfortunately TRY/CATCH
is not capable of capturing the first error; it only captures the second error.
I suspect your best bet to capture the real reason behind a failed backup is to automate your backups through SQLCMD (with -o
to send output to a file), SSIS, C#, PowerShell etc. All of which will give you much greater control over capturing all of the errors.
The SO answer in the comment suggests using DBCC OUTPUTBUFFER
- while it's possible, this does not seem like child's play at all. Feel free to have fun with this procedure from Erland Sommarskog's site, but this still doesn't seem to work well in combination with TRY/CATCH
.
The only way I seemed to be able to capture the error message with spGET_LastErrorMessage
is if the actual error does get thrown. If you wrap it in a TRY/CATCH
the error gets swallowed and the stored procedure does nothing:
BEGIN TRY
EXEC sp_executesql N'backup that fails...';
END TRY
BEGIN CATCH
EXEC dbo.spGet_LastErrorMessage;
END CATCH
In SQL Server < 2012 you can't re-raise the error yourself, but you can in SQL Server 2012 and newer. So these two variations work:
CREATE PROCEDURE dbo.dothebackup
AS
BEGIN
SET NOCOUNT ON;
EXEC sp_executesql N'backup that fails...';
END
GO
EXEC dbo.dothebackup;
EXEC dbo.spGET_LastErrorMessage;
Or in 2012 and above, this works, but to a large degree defeats the purpose of TRY/CATCH
, since the original error still gets thrown:
CREATE PROCEDURE dbo.dothebackup2
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
EXEC sp_executesql N'backup that fails...';
END TRY
BEGIN CATCH
THROW;
END CATCH
END
GO
EXEC dbo.dothebackup2;
EXEC dbo.spGET_LastErrorMessage;
In both of these cases, the error is still thrown to the client, of course. So if you're using TRY/CATCH
to avoid that, unless there is some loophole I'm not thinking of, I'm afraid you'll have to make a choice... either give the user the error and be able to capture details about it, or suppress both the error and the actual reason.
I believe it has something to do with inadequate disk space. Can you check your application/system event viewer logs if it's throwing an insufficient disk space error? Also figure out the mdf & ldf file sizes of the original database.
If you have created the database already on the 2nd server, check to make sure that file growth is not disabled.
One other option you can try is attaching the mdf file if you can copy that over to the restoring server and see if that works.
Best Answer
Ok, my bad. :-/ SQL Server is correct on both accounts.
The weekly full backup starts at 6 PM on Sundays, and the daily incremental backup starts at 8 PM. The weekly backup is still running when the daily incremental starts. The daily incremental records a .bak file that is the same size as the full, because ?? all indexes, statistics and so on have changed.
At this point, or rather, some two hours later, when it has finished reorganizing indexes and updating statistics, there isn't enough disk left for the full backup.
The incremental backup that I checked with RESTORE VERIFYONLY is ok. The full backup was never created.
So now I have to choose between some options: