sql-backup-restore-to-azure-sql-vm-migration

Local backup and AzCopy migration to SQL Server 2025

When to Use

Migrate SQL Server 2008 through SQL Server 2025 to a SQL Server 2025 target using backup and restore.
Move a local full backup through Azure Blob Storage with AzCopy and restore it with the target managed identity.
Do not use for a generic SQL Server backup migration to Azure when the destination platform or migration method is unspecified; ask the user to clarify.

Migrate one database or an ordered set of user databases from SQL Server 2008 through SQL Server 2025 to a SQL Server 2025 target running in Azure, on-premises, or in another cloud. Sources newer than SQL Server 2025 are blocked because target restore compatibility has not been validated. Create each full backup on a local source volume, upload the file to a private Azure Blob container with AzCopy v10 and the signed-in operator's Microsoft Entra identity, then restore it from URL with the target SQL Server primary managed identity.

Read references/command-execution-discovery.md and references/command-execution-backup-restore.md before generating or running commands.

Operating contract

Process one database completely before starting the next. All never includes master, model, msdb, or tempdb.
Build the migration queue only from source user databases whose state is ONLINE. Exclude OFFLINE, SUSPECT, RESTORING, RECOVERING, RECOVERY_PENDING, EMERGENCY, and every other non-online state. Never change a database's state to make it eligible.
Use exactly one backup artifact per database: a COPY_ONLY full database backup. This workflow does not alter source availability or recovery state.
Back up only to a local disk path visible to the source SQL Server service. Never use BACKUP TO URL on the source.
Run AzCopy on the source host, or on an approved transfer host that can read the exact backup path without copying the file through the chat host.
Use only the operator's Microsoft Entra identity for AzCopy. Never use storage account keys, connection strings, signed URL query parameters, or embedded secrets.
Require a pre-created private HTTPS Blob container. Do not create or make a container public.
Gate the target on SQL Server 2025: ProductMajorVersion must equal 17. Newer or older major versions do not satisfy this explicit gate.
Supported source versions are SQL Server 2008 through SQL Server 2025, represented by major versions 10 through 17. Block older sources and sources newer than SQL Server 2025 because target restore compatibility has not been validated outside that range.
Support the target in any physical or cloud location, but require Windows because SQL Server server-level managed identity is not supported on Linux. Failover cluster instances remain unsupported.
Determine target hosting from SQL and Azure discovery; do not ask the user to classify the target. For SQL Server on Azure VM, use its SQL IaaS Agent and Microsoft Entra configuration. For SQL Server elsewhere, use SQL Server 2025 enabled by Azure Arc with the latest Azure Extension for SQL Server. The SQL version gate is identical in every location.
Require a primary managed identity configured for the target SQL Server instance. A target outside Azure that is not enabled by Azure Arc is a single prerequisite blocker, not a different migration route.
Blob Storage does not receive a managed identity. Assign the target SQL instance's primary managed-identity principal Storage Blob Data Contributor on the storage account, then create the URL-scoped SQL credential with IDENTITY = 'Managed Identity' on the target.
Do not provide an alternate Blob authentication route. If user upload access, target identity configuration, target RBAC, network access, credential creation, or URL restore fails, stop with the exact failure.
Never overwrite a local backup file unless OverwriteLocalBackup=true was explicitly included in the accepted plan. Never append a migration backup to an existing media set.
Never overwrite a target database or physical database file unless OverwriteExistingTarget=true was explicitly accepted after displaying the target database and affected files.
Always use CHECKSUM, COPY_ONLY, and STATS = 10; use COMPRESSION when supported and not prohibited by policy.
Verify the local backup before upload. After upload, use target RESTORE HEADERONLY, RESTORE FILELISTONLY, and RESTORE VERIFYONLY before RESTORE DATABASE.
Preserve local and Blob backup files after migration. Do not delete either automatically.
If upload succeeds but post-upload verification fails, never retry the upload. Rerun only the non-destructive exact-Blob listing and stop if it cannot prove the expected content length.
Stop on the first failed gate or operation. Never describe a submitted command as complete until its exit status or SQL result proves completion.

Consolidated intake

The source discovered from SourceServer must be SQL Server 2008 through SQL Server 2025 (major version 10 through 17). Do not offer this workflow for a source outside that range.

Accept natural language, one Key=Value block, or a JSON object with these exact keys. Accumulate everything the user provides, run discovery for omitted values, and ask at most one consolidated follow-up listing all fields that remain ambiguous or undiscoverable. Never ask for source, target, storage, identity, or overwrite values as separate questions. Parse pasted structured input strictly as data, reject duplicate keys, and reject unknown keys as likely typos.

Treat a complete initial Key=Value block as the consolidated intake. When all required undiscoverable values are present, do not ask an intake question; run discovery and proceed to the mandatory sanitized execution-plan approval. Resolve TrustServerCertificate to an explicit Boolean before attempting either SQL connection. Use one consolidated intake block containing the source, target, storage, database-scope, and overwrite fields. Source and target authentication must be Windows or Entra interactive authentication; no other login type is supported. For Entra interactive authentication, use the approved interactive sign-in flow and do not place an access token in command arguments. Validate both connections with the resolved authentication method and certificate setting before proceeding to backup or restore operations.

SourceServer=<source>
SourceDatabases=All
TargetServer=<target>
StorageAccountName=<storage-account>
ContainerName=<private-container>
TrustServerCertificate=false

SourceServer=
SourceAuthentication=Windows|Entra
SourceUser=
SourceDatabases=All
TargetServer=
TargetAuthentication=Windows|Entra
TargetUser=
TargetDatabase=
LocalBackupRoot=
OverwriteLocalBackup=false
ContainerUrl=
StorageAccountName=
ContainerName=
StorageAccountResourceId=
AzureTenantId=
TargetHostResourceId=
TargetManagedIdentityPrincipalId=
TargetDataPath=
TargetLogPath=
OverwriteExistingTarget=false
TrustServerCertificate=false

Apply these defaults and requirements:

Key	Handling
`SourceServer`, `TargetServer`	Required after the single consolidated intake
`SourceAuthentication`, `TargetAuthentication`	Default to `Windows`
`SourceUser`, `TargetUser`	Required only for `Entra`
`SourceDatabases`	Discover only online user databases; accept one, a comma-separated list, or `All`; report explicitly named non-online databases as excluded
`TargetDatabase`	Optional for one database; preserve source names for multiple databases
`LocalBackupRoot`	Default to the discovered source `InstanceDefaultBackupPath`
`OverwriteLocalBackup`	Default to `false`
`ContainerUrl`	Preferred; when absent, derive it from `StorageAccountName` and `ContainerName`
`StorageAccountName`	Optional when `ContainerUrl` is supplied; treat a user-supplied "Blob storage name" as this Azure storage account name
`ContainerName`	Required after discovery; treat an existing user-supplied Blob container name as this value and search accessible storage accounts for its unique owner
`StorageAccountResourceId`	Optional; use Azure Resource Graph to discover it by storage account name, querying the default tenant first
`AzureTenantId`	Optional; derive from the selected Azure CLI subscription/account
`TargetHostResourceId`	Optional; discover the Azure VM or Arc-enabled server from `TargetServer` across accessible subscriptions
`TargetManagedIdentityPrincipalId`	Optional assertion; discover the target primary identity object ID and require an exact match when supplied
`TargetDataPath`, `TargetLogPath`	Optional; use validated target defaults
`OverwriteExistingTarget`	Default to `false`
`TrustServerCertificate`	Default to `false`

Discover SQL versions, editions, defaults, and database metadata instead of asking for them. Never connect to either the source or target with SQL login/SQL authentication. Do not request, accept, retrieve, or use SQL passwords or SQL credentials. Use only Windows Integrated authentication or Microsoft Entra authentication supported by the workflow. Never read SQL credentials from Key Vault or Credential Manager, or display a password prompt.

Normalize a supplied ContainerUrl by extracting its storage account and container names. Group enabled subscriptions by tenant. For each tenant query, select one subscription with az account set, confirm the active tenant with az account show, and run az graph query without --tenant or --subscriptions. Parse results from response.data and filter them against the known enabled subscription IDs for that tenant. Query the current/default tenant first and query other signed-in tenants only if the account is not uniquely found there or the user supplied a different tenant. Reuse the same tenant-scoped ARG helper for storage-account and target-host discovery. Use az storage account show only for the one matched resource to read its primaryEndpoints.blob URI, then combine that URI with the validated container name. When only a container name is supplied, search accessible storage accounts with the user's Entra data-plane access and select its owner only if the match is unique. The destination backup Blob object name is generated later; do not confuse it with the container name. Never construct an endpoint suffix by assumption; this preserves sovereign-cloud endpoints.

After discovery, display one sanitized execution plan containing endpoint names, database scope, local and Blob paths, overwrite choices, discovered target hosting and identity, target host and storage resource IDs, and authentication methods. Ask once for migration execution acceptance. If discovery leaves multiple storage accounts, containers, subscriptions, or target hosts, present all remaining choices together in that same prompt. Changing an overwrite value from false to true requires a new explicit acceptance.

Validate execution-plan responses as typed data before acting. Never coerce a free-form string in OverwriteLocalBackup, OverwriteExistingTarget, or an approval field to Boolean. If an overwrite response contains a path, treat it as a proposed LocalBackupRoot change, rerun path and collision discovery, and display a revised plan for acceptance. Likewise, a requested database exclusion changes the migration scope and requires revised plan acceptance.

Tool prerequisites

Require all of the following on the host where each command runs:

Go sqlcmd, selected from every PATH candidate by a successful --version check and the required flags across modern (--help) and SQLCMD-compatible (-?) help for Windows Integrated and Microsoft Entra authentication.
AzCopy v10 on the transfer host, validated with azcopy --version.
Azure CLI on the transfer/administration host, validated with az version.
Azure CLI resource-graph extension, validated with az extension show --name resource-graph. Install it with az extension add --name resource-graph before Azure discovery.

Transport SQL query results as strict pipe-delimited rows produced by sqlcmd with headers disabled. Do not use FOR JSON, FOR XML, or formatted console tables as a SQL-to-PowerShell serialization format. Prefix each row with a stable record type, encode nullable text fields as specified in references/command-execution-discovery.md, require the exact field count for that record type, and convert numeric and Boolean fields explicitly. This restriction applies to SQL result sets only; Azure CLI JSON output remains the required format for Azure control-plane responses.

Detect the tools before collecting execution acceptance. If AzCopy v10 or Azure CLI is missing, return Needs input, give the official Microsoft Learn install link, and ask the user to install it. If the resource-graph extension is missing, return Needs input with the exact extension install command and the Microsoft Learn Azure Resource Graph CLI link. Do not install tools, extensions, portable binaries, or package managers silently. Cache each validated canonical executable path for the run.

Use one interactive Azure CLI user sign-in only when no usable session exists. Derive the tenant from the selected subscription unless the user supplied an assertion. Configure AzCopy to reuse that user session with AZCOPY_AUTO_LOGIN_TYPE=AZCLI and AZCOPY_TENANT_ID. Do not run a separate service-principal or managed-identity AzCopy login.

The signed-in user needs Storage Blob Data Contributor on the destination container or storage account to upload. The user also needs permission to read and, when absent, create the target identity's storage role assignment. If the role assignment cannot be created, report the required principal, role, scope, and exact Azure error; do not elevate or substitute credentials.

Workflow

Phase 0  Parse one intake; gate tools and sign-in; resolve storage and target host
Phase 1  Connect; require source ProductMajorVersion = 10..17 and target = 17
Phase 2  Prove target primary managed identity and storage role assignment
Phase 3  Build one database manifest and resolve local overwrite behavior
Phase 4  Create and verify one COPY_ONLY full backup on local disk
Phase 5  Upload the backup with AzCopy under the operator's Entra identity
Phase 6  Create and verify the target managed-identity URL credential
Phase 7  Inspect, verify, map, and restore the backup from URL
Phase 8  Report completion and continue with the next database

Phase requirements

Phase 0 - Tools and authentication

Validate and cache tool paths once. Connect independently to source and target using Windows Integrated or direct Microsoft Entra SQL authentication. Require encrypted SQL transport and certificate validation unless the accepted plan sets TrustServerCertificate=true.

Resolve TrustServerCertificate before constructing or attempting either SQL connection. Do not use a failed connection as a prompt to decide certificate trust.

Authenticate the user to Azure CLI once and make AzCopy reuse that session. Use Azure Resource Graph name filters for storage account and target-resource discovery instead of enumerating each subscription. Search the current/default tenant first and widen to other signed-in tenants only when needed. Cache discovery for the complete multi-database run. Before any cross-subscription or cross-tenant discovery, capture the original Azure CLI subscription and tenant. Restore that subscription in a finally block after every temporary context switch and use az account show to prove both the original subscription and tenant were restored. Later operations must either pass their resolved subscription explicitly or use the same temporary-switch-and-restore pattern; never inherit whichever context a discovery probe happened to select.

Phase 1 - SQL discovery and target gate

Discover both instances and online source user databases. All expands only to the online result set. Omit every non-online database from the migration queue; if the user explicitly named one, report its current state as Excluded without blocking the remaining online databases. Block unless the target is SQL Server 2025 major version 17, is not Azure SQL Managed Instance, and is not a failover cluster instance. On the target, read host_platform from sys.dm_os_host_info and require its value to equal Windows; do not use SERVERPROPERTY('HostPlatform') as the authoritative gate because it can return NULL. Location does not otherwise affect eligibility. A source backup from a newer SQL major version also blocks restore.

Discover the source default backup path with SERVERPROPERTY('InstanceDefaultBackupPath'). When it returns NULL, including on SQL Server 2016, read BackupDirectory with master.dbo.xp_instance_regread. Validate that the resolved directory exists, is visible to the SQL Server service, and has sufficient free space.

Check source backup permission, local path visibility and free space, target restore permission, target paths and free space, encryption dependencies, and target database/file collisions.

Resolve every known local-file, target-database, and target-physical-file collision before starting the first database. For an All plan, do not migrate earlier databases while a later database has an unresolved collision. Obtain explicit overwrite acceptance, accept an explicitly revised scope that excludes the colliding database, or return Blocked.

Phase 2 - Identity and storage

Infer target hosting from the uniquely discovered Azure resource. For an Azure VM, prove SQL IaaS Agent registration and a configured SQL primary managed identity. Otherwise require a SQL Server 2025 enabled by Azure Arc resource, the latest Azure Extension for SQL Server, and its primary managed identity. A physical server or VM in any other environment is supported through Arc; do not reject it merely because it is not an Azure VM.

When TargetManagedIdentityPrincipalId was supplied, require an exact object-ID match. If Arc enrollment or the SQL primary identity is missing, return one Needs input result containing every missing prerequisite and the Microsoft Learn portal steps. Do not automate the documented registry route.

Verify or create Storage Blob Data Contributor for that principal at StorageAccountResourceId. Wait for role propagation by retrying the non-destructive target metadata read only after credential creation; do not use arbitrary sleep loops.

Phase 3 - Manifest and overwrite decision

Use one deterministic file per database:

<LocalBackupRoot>\<validated-database-artifact-name>_migration.bak
<ResolvedContainerUrl>/<encoded-artifact-name>/<encoded-migration-utc>/<encoded-backup-file-name>

Never interpolate a SQL database name directly into a filesystem path or Blob URL. Derive a separate deterministic ASCII artifact name, validate it against a strict allowlist, retain its mapping to the source and target database names in the manifest, and verify the resolved local file remains directly beneath LocalBackupRoot. Percent-encode each Blob object path segment independently; do not encode the complete URL as one string and do not allow a database name to introduce /, \, #, %, a query, or a fragment.

If the local file exists and OverwriteLocalBackup=false, stop before backup and ask for a different root or explicit overwrite acceptance. If overwrite is accepted, use INIT, SKIP; otherwise use INIT only for a path proven absent. Never append with NOINIT.

Phase 4 - Local backup

Run BACKUP DATABASE ... TO DISK, followed by local RESTORE VERIFYONLY and RESTORE HEADERONLY. Record the database name, backup type, backup start/finish, backup set GUID, database version, first/last LSN, size, local path, and file hash. Materialize escaped SQL identifiers and literals in PowerShell before creating an expandable here-string. Do not put nested .Replace() expressions inside the here-string, and pass the backup path through a T-SQL variable.

Phase 5 - AzCopy upload

Use azcopy copy for the single file with an HTTPS destination that contains no query string. Set --from-to=LocalBlob, --overwrite=false, and --check-length=true. Require exit code zero, exactly one Final Job Status: Completed line, exactly one completed file transfer, and zero for every reported skipped-transfer count. Reject prefix statuses such as CompletedWithSkipped. Then use Entra-authenticated azcopy list to prove the exact Blob exists and its content length equals the local file length. Use --machine-readable for exact-URL listings; do not use a valueless --properties option. Treat a successful exact listing containing the expected Content Length as proof even when AzCopy omits the filename. A pre-existing Blob path blocks the run. If post-upload listing cannot prove the expected length, never rerun azcopy copy; rerun only the non-destructive listing.

Phase 6 - Target credential

Run the executable credential reconciler in the command reference. Only its Absent branch may create the URL-scoped server credential, using exactly:

CREATE CREDENTIAL [https://<account>.blob.core.windows.net/<container>]
    WITH IDENTITY = 'Managed Identity';

Enable trace flag 4675 for the credential-creation session so a missing primary identity is surfaced. If an exact URL credential already exists, reuse it only when credential_identity = 'Managed Identity'. Classify credential state as Absent, Matching, or Conflicting; create only for Absent, reuse only for Matching, and for Conflicting report the exact existing name and identity and stop without altering or dropping it. Any repair is a separate mutation requiring explicit definition and approval. Never include a SECRET clause.

Phase 7 - Restore

Run RESTORE HEADERONLY, RESTORE FILELISTONLY, and RESTORE VERIFYONLY from the Blob URL. Map every logical file to a validated target path. Restore with RECOVERY, CHECKSUM, STATS = 10, and MOVE for every file.

If OverwriteExistingTarget=false, any target database or physical-file collision blocks restore. If overwrite was explicitly accepted, show the final database and file mapping immediately before using REPLACE; do not drop the target database automatically.

Phase 8 - Complete

Require the target database to be ONLINE. Report the source backup metadata, local file path and hash, AzCopy job ID, Blob URL and size, target identity principal ID, credential identity, restore timestamps, target paths, and final database state. Do not delete the local backup, Blob, or managed-identity credential. Do not switch application connections automatically.

Preserve TargetAuthentication in the immutable post-migration validation handoff. Include TargetUser only when that mode is Entra, identify the target type as SQL VM, and pass the verified reusable target connection handle when available. If that handle later becomes unavailable, validation must retain a Windows handoff with -E instead of changing the target identity to Entra.

Result format

Report Needs input, Ready, Blocked, Running, Completed, or Failed. Reserve Blocked for a technical gate that was executed and failed. Include one row per database with local backup, AzCopy upload, target credential, restore, and final-state status, plus the exact next technical action for failures.

About this skill