LLI Wiki — My Digital Knowledge Base

Copying an Azure SQL database between servers and environments: permissions, renaming, cleaning

Azure SQL database copy with PowerShell or T-SQL, required permissions, temporary name, sensitive data masking and post-copy steps.

Copying an Azure SQL database from one server or environment to another is a daily request: refresh a test database, prepare an acceptance environment, reproduce an incident. The copy service does the heavy part; the DBA's job is everything around it: permissions, naming, sensitive data and finishing touches.

Principle

flowchart LR
    A["Source database (server A)"] --> B["Transactionally consistent copy"]
    B --> C["Target database (server B), temporary name"]
    C --> D["Sensitive data cleaning"]
    D --> E["Rename"]
    E --> F["Users, size, retention"]
    F --> G["Database ready for the team"]

The copy is asynchronous and transactionally consistent: the result matches the state of the source at the end of the copy. The source stays usable during the operation.

Starting the copy

Two routes, depending on the context.

# PowerShell: same subscription
New-AzSqlDatabaseCopy -ResourceGroupName rg-dba-prd -ServerName sql-contoso-prd `
  -DatabaseName AppDb `
  -CopyResourceGroupName rg-dba-dev -CopyServerName sql-contoso-dev `
  -CopyDatabaseName AppDb_copy -ElasticPoolName pool-elp-01
-- T-SQL: run in the master database of the target server
CREATE DATABASE AppDb_copy AS COPY OF [sql-contoso-prd].AppDb;

-- Follow-up
SELECT name, state_desc FROM sys.databases WHERE name = 'AppDb_copy';

For a copy to another subscription, look at the T-SQL route first, with its authentication requirements: read the official page before building the process.

Permissions

Renaming

I always copy to a temporary name, then rename at the end. A half-cleaned database is therefore never visible under the name applications expect.

-- In master of the target server
ALTER DATABASE AppDb_copy MODIFY NAME = AppDb;

The rename drops open connections: do it when nobody is connected.

Cleaning sensitive data

A production copy contains personal data. Before opening access to other teams, I mask it in the copy, while only the automation can reach it.

UPDATE dbo.Customer
SET Email    = CONCAT('user', CustomerId, '@example.invalid'),
    Phone    = NULL,
    LastName = CONCAT('Name', CustomerId);

TRUNCATE TABLE dbo.AuditTrail;

Rules: the sensitive column list lives in a versioned file, the script is re-runnable, and a failure means deleting the copy rather than delivering a half-masked database.

Post-copy finishing

  1. Users: not all accounts of the source are valid on the target server. Recreate the expected users and roles, and remove those that must not exist outside production.
  2. Size and service level: scale down to what a non-production environment needs.
  3. Backup retention: check the new database's policy, it is not necessarily the source's.
  4. Server-level settings (auditing, firewall, encryption): they depend on the target server, not on the database.
  5. Tags and inventory: register the new database in the inventory.

Pitfalls and lessons learned

Mastodon LLIMastodon SocialFosstodon