BACKUP : Option to suppress info messages

BACKUP : Option to suppress info messages by aaronbertrand

Active

Type:	Suggestion
ID:	266957
Opened:	4/2/2007 8:05:04 AM
Access Restriction:	Public

Workaround(s)

view

It would be nice to have a WITH option for the BACKUP command that allows us to suppress informational messages. If the backup works fine, there is no reason.

The problem I'm specifically having is that I have a job which backs up 40 databases in one step, and when there are failures, because the SQL Agent log only keeps about 1024 characters per step, this means that unless an error happens on the very first database, I do not see the reason the step failed, since it is pushed down the stack by all the "10 percent complete" nonsense, which is pretty useless when not called directly from Management Studio, since I am not actively monitoring the output when Backup Database is called from my job or a maintenance plan.

The problem varies slightly with the database name, e.g. if you named your databases "x" and "y" you might see some details for the second database, but I doubt it.

This will also help performance as the system can prevent all that I/O at each intreval, success messages, etc.

Details (expand)

English

SQL Engine

Like many DBCC commands, something like:

BACKUP DATABASE [...] WITH NO_INFOMSGS;
--or
BACKUP DATABASE [...] WITH ONLY_ERRORMSGS;

Improved Reliability

Improved Administration

Improved Performance

0 attachments

Posted by aaronbertrand on 10/23/2007 at 11:02 AM

Thanks for the comments!

If I had to choose one, it would have to be WITH NO_INFORMATIONAL at the statement level. This is both because I want the most granular control possible and also because I think this has the least impact on the codebase...

Posted by Microsoft on 10/22/2007 at 4:16 PM

I'm looking at the best method for accomplishing this:
Would you see the best level to set this to be at the individual backup command (WITH NO_INFORMATIONAL)
or at the database, or instance levels?
If you had to choose ONE, which would it be?

Posted by Microsoft on 10/22/2007 at 3:34 PM