9 October 2026, 06:51 PM
So a buddy of mine works IT for a mid-size trucking company out in Ohio. A few months back, his boss told him they were moving their whole SQL Server setup off the old on-prem machine and into Azure. He thought, "Cool, I'll knock this out over the weekend." Famous last words, right?
Monday morning he's still at it. Tuesday he's reinstalling SQL Server on a test box because he messed something up. By Wednesday he'd broken two test databases, had a backup file that wouldn't restore, and his operations manager was blowing up his phone because the dispatch system was down. He called me Thursday basically begging for help. I've been doing this stuff for years and even I felt bad for him. The kicker? The actual migration itself was maybe a two-hour job once we used the right approach. He just went down the wrong road first.
The Free / Manual Way (in detail):
Look, you can do this for free. Plenty of people do. Here's what that actually looks like:
Back up the source database. Open SSMS, right-click the database, go to Tasks, then Back Up. Pick a folder with enough space and let it run.
Move the .bak file to wherever you're going. If it's another server on the same network, just copy it over. If you're heading to Azure, you'll need to push it to Blob Storage first.
Restore it on the other end. Right-click Databases, hit Restore Database, point it at your file, and cross your fingers.
For Azure SQL, it's a different animal. You usually can't just restore a .bak file there. Most folks use SqlPackage.exe to export a BACPAC and then import it, or they use the "Deploy Database to Microsoft Azure SQL Database" wizard inside SSMS.
Fix all the stuff that broke. Logins, users, permissions, triggers, stored procedures you name it. This part is almost always manual and it's where people lose the most time.
Move the data. Either the restore handles it, or you're using the Import/Export Wizard or bcp to push rows over.
Update your apps. Connection strings, firewall rules, linked servers all of it has to be changed or nothing works.
The Complexity and Common Mistakes:
Here's the part nobody warns you about:
Corrupt MDF or NDF files. If the source database is damaged and older servers often are a normal restore just dies. There's no "skip the broken pages" button in native tools. You're stuck.
Version mismatch. You can't take a backup from SQL Server 2019 and restore it onto 2012. People find this out after they've already told everyone to log off.
Collation conflicts. This one gets everybody. Source database uses one collation, target uses another, and suddenly your temp tables and joins are throwing weird errors you've never seen before.
Orphaned users. After a restore, logins don't always match up to database users. Permissions silently break and nobody notices until something important fails.
Missing objects. Triggers, functions, foreign keys sometimes they just don't come over. You find out the hard way when production breaks.
Azure SQL limitations. Azure SQL Database doesn't do cross-database queries, some system stored procedures, or SQL Agent jobs the same way. And BACPAC imports choke on big or corrupted databases.
Downtime. A multi-gigabyte restore can take hours. One failure means you're starting over from scratch.
End result: rollbacks, weekend work, and that lovely "well, it worked in test" conversation with your boss.
After watching that whole disaster, I started telling people about Kernel MySQL Migration Server it handles healthy and corrupted MDF/NDF files, lets you preview objects before you commit, and pushes straight to Azure SQL, Amazon RDS, or a local SQL Server in just a few clicks. Would've saved my buddy five days of pain.
Monday morning he's still at it. Tuesday he's reinstalling SQL Server on a test box because he messed something up. By Wednesday he'd broken two test databases, had a backup file that wouldn't restore, and his operations manager was blowing up his phone because the dispatch system was down. He called me Thursday basically begging for help. I've been doing this stuff for years and even I felt bad for him. The kicker? The actual migration itself was maybe a two-hour job once we used the right approach. He just went down the wrong road first.
The Free / Manual Way (in detail):
Look, you can do this for free. Plenty of people do. Here's what that actually looks like:
Back up the source database. Open SSMS, right-click the database, go to Tasks, then Back Up. Pick a folder with enough space and let it run.
Move the .bak file to wherever you're going. If it's another server on the same network, just copy it over. If you're heading to Azure, you'll need to push it to Blob Storage first.
Restore it on the other end. Right-click Databases, hit Restore Database, point it at your file, and cross your fingers.
For Azure SQL, it's a different animal. You usually can't just restore a .bak file there. Most folks use SqlPackage.exe to export a BACPAC and then import it, or they use the "Deploy Database to Microsoft Azure SQL Database" wizard inside SSMS.
Fix all the stuff that broke. Logins, users, permissions, triggers, stored procedures you name it. This part is almost always manual and it's where people lose the most time.
Move the data. Either the restore handles it, or you're using the Import/Export Wizard or bcp to push rows over.
Update your apps. Connection strings, firewall rules, linked servers all of it has to be changed or nothing works.
The Complexity and Common Mistakes:
Here's the part nobody warns you about:
Corrupt MDF or NDF files. If the source database is damaged and older servers often are a normal restore just dies. There's no "skip the broken pages" button in native tools. You're stuck.
Version mismatch. You can't take a backup from SQL Server 2019 and restore it onto 2012. People find this out after they've already told everyone to log off.
Collation conflicts. This one gets everybody. Source database uses one collation, target uses another, and suddenly your temp tables and joins are throwing weird errors you've never seen before.
Orphaned users. After a restore, logins don't always match up to database users. Permissions silently break and nobody notices until something important fails.
Missing objects. Triggers, functions, foreign keys sometimes they just don't come over. You find out the hard way when production breaks.
Azure SQL limitations. Azure SQL Database doesn't do cross-database queries, some system stored procedures, or SQL Agent jobs the same way. And BACPAC imports choke on big or corrupted databases.
Downtime. A multi-gigabyte restore can take hours. One failure means you're starting over from scratch.
End result: rollbacks, weekend work, and that lovely "well, it worked in test" conversation with your boss.
After watching that whole disaster, I started telling people about Kernel MySQL Migration Server it handles healthy and corrupted MDF/NDF files, lets you preview objects before you commit, and pushes straight to Azure SQL, Amazon RDS, or a local SQL Server in just a few clicks. Would've saved my buddy five days of pain.