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
- Azure side: a role able to create databases on the target server (for example SQL DB Contributor) and to read the source.
- SQL side, with T-SQL: the identity running the copy must be able to create databases on the target server (
dbmanagerrole) and own the source database. - One identity per role: I use a managed identity for scheduled copies, and grant rights on the target server, not on the whole subscription.
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
- 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.
- Size and service level: scale down to what a non-production environment needs.
- Backup retention: check the new database's policy, it is not necessarily the source's.
- Server-level settings (auditing, firewall, encryption): they depend on the target server, not on the database.
- Tags and inventory: register the new database in the inventory.
Pitfalls and lessons learned
- An elastic pool has limits: number of databases and maximum storage. A copy failing for that reason happens more often than you would think.
- A copy already running under the same name blocks the next one: check the state before retrying, and plan a cleanup of abandoned copies.
- Users mapped to a SQL login (rather than contained in the database) become orphaned if the SID differs on the target server.
- Across regions, the copy takes longer and may incur transfer costs.
- Never copy to a less protected environment without cleaning first: the target's access control matters as much as the source's.
- Test the whole procedure on a small database before wiring it to a big one: time it, and decide the acceptable timeout.