AdventureWorks2025 to an Azure SQL VM, part 5: restore

Part 5 of 6. The series: 1. The plan and the lab · 2. Check what is there · 3. Export what the target needs · 4. Back up and upload · 5. Restore (this post) · 6. Check post-migration, and clean up.

Part 4 left everything the VM needs in one place: the backups, the facts, the key files and the inventory in Blob storage, the passwords and the logins in Key Vault. From here on the laptop is out of the picture, and every step runs on the VM.

None of it needs the Az module or a sign-in. The VM asks Azure’s metadata endpoint for a token, as its own managed identity, and uses that token against Blob storage and Key Vault directly (the roles from A3 in part 1). No key, password or certificate for Azure is ever stored on the VM.

Two scripts, in this order: 40 brings the database back, 41 brings back the login it needs.

Part F. Restore

F1. Restore from Blob: script 40

40_Restore-FromBlob.ps1 does five things, and the order is the whole point:

  1. Checks that it is not on the laptop, that it runs as an administrator who is sysadmin, and that the VM’s identity reaches both Blob storage and Key Vault.
  2. Downloads the backups (<laptop>/AdventureWorks2025/FULL/ and /LOG/) and the key files (keys/<laptop>/) to a staging folder, F:\Restore. A file already there is skipped.
  3. Installs the TDE certificate, if the VM has none with the backup’s thumbprint: first a database master key in master, with a password generated and used once, then AW2025_TDE from the .cer and .pvk, with the password read from Key Vault (aw2025-pvk-password). The key files are deleted from the staging folder straight after.
  4. Restores the newest FULL WITH NORECOVERY, to the VM’s default data and log folders, then every LOG after it, in order. A log already applied is skipped; a gap in the chain stops the script, with nothing lost: put the missing file in Blob and run it again.
  5. Recovers the database, and ends with the CHECK table.

[VM, PowerShell as administrator] As a Windows login that is sysadmin in SQL Server. -SourceServer is your laptop’s name, as in B4:

.\40_Restore-FromBlob.ps1 -StorageAccount aw2025mig12345 -KeyVault aw2025-kv-1234 -SourceServer <your laptop's name> -WhatIf
.\40_Restore-FromBlob.ps1 -StorageAccount aw2025mig12345 -KeyVault aw2025-kv-1234 -SourceServer <your laptop's name>

Two switches for the real thing:

  • -NoRecovery leaves the database RESTORING. Restore the FULL days before the cutover, then run 40 again after the last LOG is in Blob: it carries on from where it stopped and applies only what is new.
  • -Replace restores over an ONLINE database of the same name on the VM, and asks you to type the name first. Without it, 40 refuses to touch an existing database.

The certificate comes first, even to read the file list. My first run stopped before restoring anything: RESTORE FILELISTONLY failed with Cannot find server certificate with thumbprint …. I had expected the certificate to matter for the restore itself, not for reading which files are in the backup. On SQL Server 2025 it matters for both. So 40 installs the certificate before it opens the backup at all, and then compares the thumbprint inside the backup with the certificate it has just installed.

Mine downloaded four files (the FULL, the LOG and the two key files), created the master key, installed AW2025_TDE, restored the FULL and one LOG, and brought AdventureWorks2025 ONLINE and encrypted, at the laptop’s time of the last LOG backup.

Expected: a CHECK table of four lines, all OK: the database ONLINE (or RESTORING with -NoRecovery), encrypted with the laptop’s certificate, restored from the laptop’s backups, and the key files gone from the staging folder. Then All OK. AdventureWorks2025 is ONLINE on <VM>. Next: 41_Import-Logins.ps1.

F2. The SQL logins: script 41

The database is back, and so is its user. The login that user belongs to is still only in Key Vault, in the secret aw2025-logins from C2 in part 3. 41_Import-Logins.ps1 reads it with the VM’s identity and, for each SQL login in it:

On the VMWhat 41 does
missingCREATE LOGIN ... WITH PASSWORD = <hash> HASHED, SID = <sid>, with the laptop’s default database and language, password policy and expiration flags; disabled if it was disabled there
there, with the same SIDleaves it as it is
there, with another SIDreports it and changes nothing: its user would not map. Drop it and run again, or fix the user yourself

Then it adds the server roles and server-level permissions the login had on the laptop (it adds, never removes), and lists the database’s users with the login each one maps to.
Just a side note: as previously said, this is the part that worked only partly for me (custom permissions), so keep an eye on this.

[VM, PowerShell as administrator] As a Windows login that is sysadmin in SQL Server:

.\41_Import-Logins.ps1 -KeyVault aw2025-kv-1234 -SourceServer <your laptop's name> -WhatIf
.\41_Import-Logins.ps1 -KeyVault aw2025-kv-1234 -SourceServer <your laptop's name>

If you skipped the G7 fix by hand in part 2, -EnableSqlAuth does it here: it switches the server to SQL Server and Windows authentication and restarts SQL Server and Agent.
Back in the 2020 era I would have said that it is mandatory to have mixed mode authentication, but right now I am not so sure about it anymore – Microsoft is pushing hard to a cloud-only, Entra-based architecture, which is meant to change the security game. We will see how this goes, though.

Prove the password, not just the login. A login with the right SID maps the user; it does not prove the password came across. For that, put one login’s password in Key Vault as aw2025-login-<login name in lower case>-password, and 41 signs in as that login and checks it lands in the database as its user. I skipped it in the lab, so the line for it says 0 tested.

Mine created AW2025Migration with the laptop’s SID and password hash, and the user in AdventureWorks2025 mapped to it on its own: no ALTER USER, no orphaned users, no fix-up script.

Expected: a CHECK table of six lines, all OK: every SQL login is there with the laptop’s SID, every one maps to its user, no SQL users without a login, SQL logins can sign in (authentication mode), the sign-in test passed for every login tested, and no Windows logins to decide on. Then All OK. 1 login(s) created. Next: 50_PostRestore-Check.ps1.

Check the scripts before you run them

The scripts of this post, from the v1.0 release (immutable: nobody, me included, can change its files or its tag). On the VM:

Get-FileHash C:\AW2025Migration\scripts\4*.ps1 -Algorithm SHA256 | Format-Table Hash, Path -AutoSize

Every hash must match its line below; SHA256SUMS.txt on the release has all of them. If one differs, don’t run it.

FileSHA-256
aw2025-azure-migration-v1.0.zip0db411127783f4f2c604211d8293b2854a7776b0289d54b57344890e73c45d4f
40_Restore-FromBlob.ps111578b5f373a572c14a2e855f3772c8d176cd2c1a2631120ac9ff7c52a4c2acf
41_Import-Logins.ps1d3f4641a9101635d11dee994dc48c8b7ec54c88ef77e1832b508f6b8720b3bc0

Next

Part 6 is part G, the last one: script 50 holds the restored database against the facts from part D (settings, row counts and checksums, objects, the hash of the code, users and logins), runs CHECKDB and sets the owner. Then the clean-up: 99 resets the lab, 00 takes Azure down (with a vault purge that is slower than it says), and the bits nobody tells you about closing a free account.

The scripts are on GitHub: calinsqlplay/aw2025-azure-migration (the version these posts use is the v1.0 release).

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.