Production data has a way of following an application into development and test. A database restore brings over the records needed to reproduce a bug, along with real names, email addresses, and phone numbers that nobody needed for the test. I worked through this with a client not long ago. Part of the work was sorting out what we meant by masking, because hiding a value in a query result and removing it from a database copy solve different problems.

Suppose a support application needs to display a customer's phone number with only the last four characters visible. SQL Server's Dynamic Data Masking can do that without changing the stored value:

ALTER TABLE dbo.Customer
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

ALTER TABLE dbo.Customer
ALTER COLUMN Phone ADD MASKED WITH (FUNCTION = 'partial(0, "xxx-xxx-", 4)');

A reader without UNMASK permission sees the masked values. Accounts with that permission, including database owners, can see the originals. Test this using the account that actually reads the data; checking it as an administrator will give you the wrong impression.

This helps limit routine exposure, but someone allowed to run arbitrary queries may still infer the underlying values. Microsoft explicitly warns about that limitation. Keep access restricted to what the application or person needs.

And a backup still contains the real data. Restore it to development and the original names, emails, and phone numbers come with it, regardless of the masks.

For that copy, the job is static masking: replacing the sensitive values themselves. On the client project, we used Microsoft Purview to help identify columns containing sensitive data, then wrote T-SQL to scrub them. Classification helped us find the work; the update scripts did the replacement.

The replacement rules deserve some thought. Keeping the first letter of a name and filling the rest with x's still reveals its initial and length. Building every replacement email from that initial also creates duplicates. That can break a unique index before the masking pass finishes.

Here is a simplified example using synthetic values. It assumes CustomerId is a unique, non-null integer key, the text columns are long enough for the replacements, and Phone permits nulls. Run this only against the isolated copy being sanitized:

UPDATE dbo.Customer
SET FirstName = CASE WHEN FirstName IS NULL THEN NULL
                     ELSE 'Test' END,
    LastName  = CASE WHEN LastName IS NULL THEN NULL
                    ELSE 'Customer' + CONVERT(varchar(20), CustomerId) END,
    Email     = CASE WHEN Email IS NULL THEN NULL
                    ELSE 'customer.' + CONVERT(varchar(20), CustomerId)
                         + '@example.invalid' END,
    Phone     = NULL;

Existing null names and emails stay null. The generated email addresses are distinct for distinct customer IDs, and no part of the original name or address is used. The phone number is removed entirely. If the application requires phone numbers, supply synthetic test values that satisfy its rules instead.

The .invalid domain is reserved for deliberately invalid domain names. Even so, disable outbound email, SMS, and production integrations before the refresh starts. Replacing one email column does not catch a recipient stored in a notification queue or application configuration.

These replacements also change the data your tests see. A table full of short, predictable names will not exercise long names, apostrophes, or international characters. Add synthetic cases for those deliberately. Preserving bits of a customer's identity is a poor substitute for choosing the test data you actually need.

Before making the refreshed database available to developers, check more than whether the update completed:

• Verify the replacement values against column lengths, unique indexes, and application validation. Where a value appears in related tables, use a consistent replacement so those relationships still work.
• Look beyond the obvious customer columns. Free-text notes, saved request payloads, history tables, and queued messages can hold the same information in less convenient forms.
• Check the resulting data for unexpected values, and review fields that classification did not flag. Updating four columns is not evidence that the whole database is sanitized.
• Keep the source backup and any logs or other copies containing the originals protected. An UPDATE is not secure erasure, and retaining production IDs or other identifying details means you should not call the result anonymous.

Put the scripts and validation checks in source control and run them as part of every refresh. Keep the restored copy restricted until those checks pass. That way sanitizing the data is a condition of handing it over, rather than a task someone has to remember afterward.