Part 4 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 (this post) · 5. Restore · 6. Check post-migration, and clean up.
Part 3 left the laptop with AdventureWorks2025 encrypted (and the laptop in service, that CPU can go to higher temperatures than the surface of the Sun), in FULL recovery, its certificate backed up and proved, and its SQL login safe in Key Vault. Now the data itself moves: part D backs it up and writes down what it looked like at that moment, part E carries it all to Azure.
In a real migration this is where the cutover lives, so the order matters:
- Days before: a FULL backup, uploaded at leisure. On a big database this is the slow part, and it happens while the application still runs.
- At cutover: the application stops. One last LOG backup, taken after nobody can write any more.
Side note here: I am usually doing tail-LOG backups so the databases stay in Restoring. This has two benefits: no one can touch any database, and you have the basis for the restore in case you need to roll back.
However, this is just a demo, so not everything applies. - Right after it: the facts of the database, as it was at that last backup (row counts, checksums, objects). Part G holds the VM against them.
With a tail-log backup instead, take the facts just before it. - Then the upload of whatever is not in Azure yet: the last log, the facts, and anything missed.
The downtime is steps 2 to 4 plus the restore of the logs on the VM, not the size of the database. In the lab I did all four in one go, a few minutes apart: AdventureWorks2025 is 55 MB as a backup, and nobody was waiting for it.
Part D. Back up
D1. FULL and LOG backups: script 30
30_Backup-Source.ps1 takes the backups to a local folder; the upload is a separate step (E1), so a slow line never holds a backup open. Every backup is:
- checksummed and compressed (
WITH CHECKSUM, COMPRESSION), with a 4 MB transfer size; - verified straight after it is written (
RESTORE VERIFYONLY ... WITH CHECKSUM); - named the way Ola Hallengren’s jobs name them:
<server>_<database>_FULL_yyyyMMdd_HHmmss.bakand..._LOG_....trn, inD:\Backups\<laptop>\AdventureWorks2025\FULL\and\LOG\. The time in the name is the laptop’s local time, and the restore in part F reads it: that is why the VM’s time zone had to match, back in part 1.
I have to stop here for a bit: LRS work a tad differently when it comes with the folder and subfolder structures, so be mindful of that.
Nothing is ever deleted, and every run only adds backups. Before the first backup, the script gives the SQL Server service Modify on D:\Backups, since the service is the one that writes the files.
[laptop, PowerShell as administrator] As a Windows login that is sysadmin in SQL Server. No Azure sign-in is needed here:
.\30_Backup-Source.ps1 -WhatIf.\30_Backup-Source.ps1
The default is a FULL, then a LOG. At cutover, after the application stops, the last one is a LOG on its own:
.\30_Backup-Source.ps1 -Type Log
It refuses a LOG backup while there is no log chain yet: after the switch to FULL recovery in part C, the chain starts with the first FULL.
TDE and compression. For years, compressing a backup of a TDE database gained nothing: encrypted pages do not compress. Since SQL Server 2016 they do, as long as the transfer size is above 64 KB, which is why
30setsMAXTRANSFERSIZE = 4194304. On SQL Server 2016, 2017 and 2019 before CU5, leave it out and you will wonder why your compressed backup is as big as the database. Since SQL Server 2019 CU5 (I could have sworn is SQL 2022, but turns out it was earlier), the larger transfer size is no longer needed for TDE databases. Do your own testing here – for me it was 91 GB for a FULL backup against a 520+ GB database with Ola’s default backup settings.
Mine: a FULL of 55.1 MB in 4 seconds and a tiny LOG, both verified.
Expected: a CHECK table of two lines, all OK: this run’s backups were written and verified, and there is a FULL in the folder. Then All OK. Next: 31_Export-SourceFacts.ps1 (right after the last backup), then 32_Upload-ToBlob.ps1.
D2. The facts, right after the last backup: script 31
How do you know the database on the VM is the database, and not a database that happens to have the same name? You compare it with what the laptop’s database looked like at the moment of the last backup. 31_Export-SourceFacts.ps1 writes that moment down, read-only, in a small JSON file:
- the settings: collation, compatibility level, recovery model, page verify, isolation options, trustworthy and the rest, plus the owner;
- the last backup: its name, finish time and last LSN, and whether any user wrote to the database after it;
- every user table: its exact row count (
COUNT_BIG) and a checksum of its data (CHECKSUM_AGG(BINARY_CHECKSUM(*))); - the objects, by type: tables, views, procedures, functions, triggers, constraints, indexes, types, full-text indexes;
- the code: a SHA-256 hash of every view, procedure, function and trigger;
- users and logins: each user, the login it maps to, its SID and server roles (no password hashes: those are in Key Vault, from
21); SQL Agent jobs that name the database; the TDE certificate’s thumbprint.
The file goes to D:\Backups\<laptop>\AdventureWorks2025\FACTS\; 32 uploads it in E1, and 50 reads it on the VM in part G.
[laptop, PowerShell as administrator] Right after the last backup, and before anybody can write again:
.\31_Export-SourceFacts.ps1 -WhatIf.\31_Export-SourceFacts.ps1
Writes after the backup. If anything wrote to the database after the last backup, the row counts will not match on the VM, and part G will tell you so loudly.
31checks for it (the last user update SQL Server saw, against the finish time of the last backup) and warns you. The fix is the cutover order from the top of this post: stop the application,30 -Type Log, then31.
Mine, two minutes after the LOG backup: no writes after it; 71 tables with 760,174 rows between them; 89 CHECK constraints, 152 defaults, 90 foreign keys, 71 primary keys, 175 indexes, 20 views, 10 procedures, 11 functions, 10 triggers, 3 full-text indexes; and 51 modules of code, hashed.
This is where I should stop and tell you that no, don’t do that. Simply create a table with 1 column and 1 value just before the tail-LOG backup and you will be fine. Having said that, do whatever suits you.
Expected: a CHECK table of two lines, all OK: the facts file written, and no user writes after the last backup. Then All OK. Next: 32_Upload-ToBlob.ps1.
Part E. Upload
E1. Upload to Blob storage: script 32
32_Upload-ToBlob.ps1 carries everything the VM needs from the laptop into the container, with the layout from Part 0 of part 1:
| On the laptop | In the container backups |
|---|---|
D:\Backups\<laptop>\AdventureWorks2025\FULL\*.bak and \LOG\*.trn | <laptop>/AdventureWorks2025/FULL/ and /LOG/ |
D:\Backups\<laptop>\AdventureWorks2025\FACTS\*.json | facts/<laptop>/AdventureWorks2025/ |
D:\TDE-Keys\AW2025_TDE.cer and .pvk | keys/<laptop>/ |
D:\Backups\<laptop>\INVENTORY\*.json | inventory/<laptop>/ (if 10 did not upload it already) |
Passwords, password hashes and logins are not files: they went straight to Key Vault in part C, and they do not travel this way.
A file that is already in Blob with the same size is skipped, and nothing is ever deleted, locally or in Blob. So it is safe to run it after the FULL days before the cutover, and again after the last LOG: the second run uploads only what is new.
[laptop, PowerShell as administrator] Elevated, because the key folder is locked to Administrators (C1), and signed in to Azure (B1):
.\32_Upload-ToBlob.ps1 -StorageAccount aw2025mig12345 -WhatIf.\32_Upload-ToBlob.ps1 -StorageAccount aw2025mig12345
The laptop has no identity. The VM signs in to storage with its managed identity; the laptop cannot, because it is not in Azure.
32reads the storage account key with your own Azure rights, for this run only, and never writes it anywhere. For a lab that is fine. For a real migration I would go further: give the upload its own identity with write rights on the container only, keep the key files in a separate container the VM can only read, and switch account-key access off once the move is done.
Mine uploaded six files: the FULL, the LOG, the facts, the .cer, the .pvk, and an inventory from an earlier attempt (I am not supposed to tell you this, but not everything was unicorns and rainbows). All seven local files were then in Blob with the same size.
Expected: a CHECK table of three lines, all OK: every local file is in Blob with the same size, the facts were taken after the last backup, and both TDE key files are in keys/<laptop>/. Then All OK. Next, on the Azure VM: 40_Restore-FromBlob.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 laptop:
Get-FileHash C:\AW2025Migration\scripts\3*.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.
| File | SHA-256 |
|---|---|
aw2025-azure-migration-v1.0.zip | 0db411127783f4f2c604211d8293b2854a7776b0289d54b57344890e73c45d4f |
30_Backup-Source.ps1 | 01708be0cf96bed86ea90b37a29422c4b29054525d68ec6a59941ce2a5bb09ab |
31_Export-SourceFacts.ps1 | 6c5f364b8fdf22df62373212c2c4ddc74670409ba35b51714516f5cede8d7eda |
32_Upload-ToBlob.ps1 | 92ee9d6024e6550334c6181858faba0928504cd77a49daa074421d609b3f8842 |
Next
Part 5 is part F, and it all happens on the VM: script 40 installs the TDE certificate from Blob and Key Vault, then restores the newest FULL and every LOG after it, and 41 creates the SQL login with the laptop’s SID and password hash, so the user in the database maps to it with no fix-up. It is also where the one real surprise of the lab was waiting: SQL Server 2025 would not even read the file list of an encrypted backup without the certificate.
The scripts are on GitHub: calinsqlplay/aw2025-azure-migration (the version these posts use is the v1.0 release).