Home / Guides / Stop a test database from emailing customers

Stop a test or staging copy of your database from emailing real customers

You restore last night's production backup to the test server so the testers have real data. Then a scheduled job wakes up and sends invoice reminders to a few thousand real customers, from the test system. It happens more often than anyone admits. This guide shows the free ways to stop it, a script for each, and where each one stops.

Why a test copy emails real people

A copy of production carries everything that sends mail with it:

• The addresses. Customers, contacts, notification lists and "send the report to" settings are all real.
• The senders. Scheduled jobs, queue workers, triggers that call sp_send_dbmail, and rows in a pending-email table that were waiting to go out when the backup was taken.
• The settings. If the app reads its SMTP server from a settings table, the test copy now points at the production mail server.

Step 1: rewrite the addresses in the copy

This is the protection that does not depend on anyone's configuration. First find every column that can hold an address. This query lists text columns whose name suggests one, with their length:

USE [YourApp_Test];   -- the restored TEST copy, never production
GO
-- Text columns whose name suggests an email address, with their length in characters.
SELECT s.name AS SchemaName, t.name AS TableName, c.name AS ColumnName,
       TYPE_NAME(c.system_type_id) AS TypeName,
       CASE WHEN c.max_length = -1 THEN 'max'
            WHEN TYPE_NAME(c.system_type_id) IN (N'nchar', N'nvarchar') THEN CAST(c.max_length / 2 AS varchar(10))
            ELSE CAST(c.max_length AS varchar(10)) END AS MaxChars
FROM sys.columns AS c
JOIN sys.tables  AS t ON t.object_id = c.object_id
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
WHERE TYPE_NAME(c.system_type_id) IN (N'char', N'nchar', N'varchar', N'nvarchar')
  AND (c.name LIKE N'%mail%' OR c.name LIKE N'%recipient%' OR c.name LIKE N'%notif%')
ORDER BY s.name, t.name, c.name;

Then append an ending to every address. .invalid is a top-level domain reserved by RFC 2606 that never resolves, so mail to jane.doe@contoso.com.invalid cannot be delivered anywhere, and the original address stays readable. Appending, rather than replacing the domain, also keeps addresses unique, so a unique index on the column does not break. Run it on the test copy right after the restore, before the app or SQL Server Agent starts:

USE [YourApp_Test];
GO
-- One batch on purpose: the guard, the updates and the check run together or not at all.
SET XACT_ABORT ON;   -- any error (for example a column too short) rolls everything back

IF DB_NAME() <> N'YourApp_Test'
    THROW 50000, N'This is not the test copy. Nothing was changed.', 1;

DECLARE @Suffix varchar(60) = '.invalid';   -- or a test inbox ending, see below

BEGIN TRANSACTION;

UPDATE dbo.Customers
SET    Email = LTRIM(RTRIM(Email)) + @Suffix
WHERE  Email LIKE '%_@_%'
  AND  Email NOT LIKE '%' + @Suffix;

UPDATE dbo.Orders
SET    NotifyEmail = LTRIM(RTRIM(NotifyEmail)) + @Suffix
WHERE  NotifyEmail LIKE '%_@_%'
  AND  NotifyEmail NOT LIKE '%' + @Suffix;

COMMIT TRANSACTION;

-- Lists of addresses in one column ("a@x.com; b@y.com") got the ending on the last address only: find them.
SELECT CustomerId, Email FROM dbo.Customers WHERE Email LIKE '%@%[;,]%@%';

What the script takes care of, and what you still have to check:

• The guard. It stops before changing anything if it is connected to any database other than the test copy.
• Safe to run twice. Addresses that already end in the suffix are skipped, as are NULL, empty and non-address values.
• All or nothing. With XACT_ABORT on, an error such as "String or binary data would be truncated" (a column too short for the longer address) rolls back every update. Widen that column on the test copy and run it again.
• Lists of addresses in one column get the ending on the last address only. The last query finds them; fix those rows separately.
• Close the window. Restore with WITH RESTRICTED_USER so only db_owner, dbcreator and sysadmin members can connect until the script has run, then set the database back to MULTI_USER. Note that an app that logs in as db_owner still gets in.

If the database sends mail itself with Database Mail, also stop it on the test server. sp_send_dbmail keeps accepting mail and queues it, so clear the queue before anyone starts Database Mail again:

USE msdb;
GO
EXECUTE dbo.sysmail_help_status_sp;   -- STARTED or STOPPED
EXECUTE dbo.sysmail_stop_sp;          -- sp_send_dbmail still queues mail, but nothing is sent
GO
-- Before you ever start it again on this server, drop what queued up meanwhile:
-- EXECUTE dbo.sysmail_delete_mailitems_sp @sent_status = 'unsent';
-- EXECUTE dbo.sysmail_start_sp;

Step 2: point the test app at somewhere harmless

A pickup folder (.NET, no server needed)

System.Net.Mail.SmtpClient can write each message as an .eml file to a folder instead of sending it. In a .NET Framework app this is configuration only:

<configuration>
  <system.net>
    <mailSettings>
      <smtp deliveryMethod="SpecifiedPickupDirectory">
        <specifiedPickupDirectory pickupDirectoryLocation="C:\MailDrop" />
      </smtp>
    </mailSettings>
  </system.net>
</configuration>

In a modern .NET app, set DeliveryMethod and PickupDirectoryLocation on the client from your own setting. This PowerShell shows the effect; nothing leaves the machine, and the folder must exist (the client does not create it, and sending fails without it):

$pickup = 'C:\MailDrop'
New-Item -ItemType Directory -Path $pickup -Force | Out-Null

$smtp = New-Object System.Net.Mail.SmtpClient
$smtp.DeliveryMethod = [System.Net.Mail.SmtpDeliveryMethod]::SpecifiedPickupDirectory
$smtp.PickupDirectoryLocation = $pickup
$smtp.Send('noreply@yourapp.example', 'jane.doe@contoso.com', 'Your invoice', 'Test body')
$smtp.Dispose()

Get-ChildItem $pickup -Filter *.eml | Sort-Object LastWriteTime | Select-Object -Last 1 |
    Get-Content -TotalCount 8

The file keeps the real recipient, so you can see who would have received it:

X-Sender: noreply@yourapp.example
X-Receiver: jane.doe@contoso.com
MIME-Version: 1.0
From: noreply@yourapp.example
To: jane.doe@contoso.com
Date: 13 Nov 2026 16:02:57 -0800
Subject: Your invoice
Content-Type: text/plain; charset=us-ascii

A local catcher: Mailpit or smtp4dev

For an app that only knows "SMTP host and port", run a catcher: a small SMTP server that keeps everything it receives and shows it in a web page. Both of these are free and open source. Mailpit listens for SMTP on port 1025 and serves its web inbox on port 8025; smtp4dev installs on Windows with winget install smtp4dev or runs in Docker:

# Mailpit: SMTP on 1025, web inbox on http://localhost:8025
docker run -d --restart unless-stopped --name=mailpit -p 8025:8025 -p 1025:1025 axllent/mailpit

# smtp4dev: SMTP on 2525, web inbox on http://localhost:5000
docker run --rm -it -p 5000:80 -p 2525:25 rnwood/smtp4dev

Then set the test app's SMTP host to the catcher. Both can also release or relay caught messages to a real mail server; leave that off, or limit it to your own addresses.

Where the free ways stop

• A rewrite to .invalid loses the mail. It is safe, but nobody can see what the test system sent, so you cannot test the emails themselves.
• Configuration gets copied over. A settings table restored from production, a deploy that brings the production config, or a new app on the test server, and mail goes out again. The data rewrite is what saves you then.
• A pickup folder is one server's disk. Testers cannot browse it, and every app has to be set up for it.
• A catcher is one more server to run. On a developer's machine that is fine. For a shared test environment someone has to host it, keep it running and backed up, protect its web page (it shows real customer data from the copy), and run one per environment.
• Not everything sends through SMTP. An app that sends through a mail provider's HTTP API, or another system that reads the addresses from your copy, goes around a catcher entirely.

Keep the mail, not just stop it: a hosted test inbox

The rewrite trick works with any domain you control, so you can point the addresses at an inbox your whole team can read. With MailPier Inbox your team has an account (say acme) with environments such as qa, and you append the environment's ending instead of .invalid:

DECLARE @Suffix varchar(60) = '.qa.acme.mailpier.io';
-- jane.doe@contoso.com  becomes  jane.doe@contoso.com.qa.acme.mailpier.io

Mail to jane.doe@contoso.com.qa.acme.mailpier.io lands in the qa inbox, shown as "intended for jane.doe@contoso.com". Because the address itself points at the test inbox, this holds even when the test app is still configured with the production mail server. An address whose environment does not exist (a typo such as qaa) is not refused: it lands in the account's main inbox, marked "sent to unknown environment". You read the mail in the web inbox or with the REST API; this lists what the test copy sent in the last 12 hours and who each message was meant for:

$key   = $env:MPI_KEY   # an API key of the qa environment, kept as a secret
$since = [DateTime]::UtcNow.AddHours(-12).ToString('o')
$uri   = 'https://api.mailpier.io/v1/messages?take=100&since=' + [uri]::EscapeDataString($since)

$list = Invoke-RestMethod -Uri $uri -Headers @{ Authorization = "Bearer $key" }
$list.items | ForEach-Object {
    [pscustomobject]@{
        Received    = $_.receivedUtc
        Subject     = $_.subject
        IntendedFor = ($_.to | ForEach-Object { $_ -replace '\.qa\.acme\.mailpier\.io$', '' }) -join ', '
    }
} | Format-Table -AutoSize

Two honest limits: the mail, with whatever customer data it contains, is stored at MailPier until the environment's retention period ends, so mask sensitive data in the copy if it must not leave your network; and mail over your plan's limits is refused, not delivered anywhere else.

Questions

Related

• MailPier Inbox: test inboxes for QA and dev teams
• Transfer SQL Server logins with their SID and password
• All guides

Sources: sysmail_stop_sp, sysmail_delete_mailitems_sp, RESTORE arguments (RESTRICTED_USER), SmtpDeliveryMethod, specifiedPickupDirectory element, RFC 2606 (reserved .invalid), Mailpit in Docker, smtp4dev installation, MailPier Inbox quick start and API.