How do I temporarily disable sp_send_dbmail?

I have just taken over responsibility for a SQL 2000 system which made heavy use of xp_sendmail.

We are in the process of upgrading to SQL Server 2008 R2 and I have implemented sp_send_dbmail.

We are currently performing user acceptance testing on the new SQL Server. The application sends out many emails directly to our customers. Since our test database is a copy of our live database, there are many valid customer email addresses in our test database. Is there a way to either temporarily redirect the emails or shut down the Database Mail service, without impacting our testing?

Assuming you're sending the messages from within a few stored procedures, I'd recommend checking the database name before sending the message, and skipping that step if it's not the production database. It usually looks something like this:

IF DB_NAME() = 'production_db_name'
    --Do stuff, like sending mail
END IF

Then it will automatically skip the mailing if you're running it from the test database.

StackExchange Database Administrators Q#10749, answer score: 3