Removing any output from BACKUP operation on SQL Server

[H2Apps]

Hello,

Apparently there is no way to remove the output produced by the BACKUP command.

You can use the trace flag 3226 to remove the writing to the SQL Server's log file, and use the option

WITH STATS = 100 to reduce the number of completion messages, but you will still have a 4 lines message :

100 percent processed.

Processed 680 pages for database 'myDB', file 'myDB' on file 1.

Processed 1 pages for database 'myDB', file 'myDB_log' on file 1.

BACKUP DATABASE successfully processed 681 pages in 1.272 seconds (4.177 MB/sec).

It's really a big problem when you are calling BACKUP command from a service broker queue. Indeed, in this case, you will find in the SQL Server's log file, these 4 lines (due to the fact that a service broker queue log everything wrote to the console output)

If someone can help me on that, it would be greatly appreciated.

Or if it's not possible to suppress these rows, is there a way to ask for a new option for the BACKUP command ?

Thanks in advance!

[#2]

There is no built-in option to suppress the backup completion message entirely in SQL Server. However, you can redirect the output of the backup command to a text file or a null device to prevent the messages from being displayed on the console output.
Here is the query you can try.

BACKUP DATABASE myDB
TO DISK = 'C:\Backup\myDB.bak'
WITH INIT, SKIP, STATS = 100
> C:\Backup\BackupLog.txt

This command backs up the "myDB" database to the "C:\Backup\myDB.bak" file, skips any existing backup sets, generates progress messages every 100 percent, and redirects the output to a text file named "BackupLog.txt" in the "C:\Backup" folder.

Note that the > symbol is used to redirect the output to a file. If the file already exists, the new output will be appended to the end of the file. If you want to overwrite the file each time the command runs, you can use >> instead of >like below.

BACKUP DATABASE myDB
TO DISK = 'C:\Backup\myDB.bak'
WITH INIT, SKIP, STATS = 100
>> C:\Backup\BackupLog.txt