A SQL Server migration fails in two places: before it starts, when nobody checked whether the workload even fits the target, and after it finishes, when logins don’t match, users are orphaned, and the first Monday morning proves the move wasn’t as done as it looked on Friday. The scripts in this area cover the whole arc, assess the source, script out everything the restore doesn’t carry, and prove the target actually works before anyone calls it complete.
This post is the map. It groups the migration scripts by the question they answer, points at the runbook that matches the shape of the job, and lays out the order a real migration runs them in, assessment first, generation second, validation last.
Why Migration and Deployment Matters
- A restore moves the databases, and nothing else, logins, Agent jobs, linked servers, and user mappings all live outside the database and have to be scripted across deliberately
- Logins recreated without their original SIDs orphan every database user that mapped to them, which is the single most common day-one migration failure
- Feature usage that was fine on the source edition or version can be a blocker on the target, and finding that out after the cutover is the expensive way
- The target isn’t migrated when the databases are online, it’s migrated when logins connect, jobs run, and the application signs off
Start Here
The Scripts, Grouped by What They Answer
🔍 Will this migration bite us?
Run these before committing to anything.
- Migration Risk Assessment, the pre-flight over the source instance, what is configured, what is deprecated, what will not carry
- Version Upgrade Readiness, whether the databases and their settings are ready for the target version
- Edition Feature Usage, which edition-specific features are in use, the question that decides whether Standard on the target is even an option
📦 Carry the server over
Everything the restore does not move.
- The Migration Script Generators, the five as a family, each reads the source catalogs and produces plain DDL you review before running on the target
- Logins and User Mappings, in that order, so restored users map to the right SIDs
- Agent Jobs, Linked Servers and Restore With Move for the rest of the instance-level furniture
✅ Did it actually work?
The target is not done until these pass.
- Migration Login Audit and Post-Migration Validation, the source-vs-target reconciliation, what made it across and what did not
- Get Orphaned Users, the day-one check on every restored database
- Fix Orphaned Users, the repair for what the previous script finds
- Compatibility Levels, what the number actually controls, the decision an upgraded database is still waiting on once it is online
📜 The full procedure, end to end
The ordered walkthroughs these scripts slot into.
- Standalone Instance Migration, moving a standalone instance to new hardware, the most common migration there is
- Availability Groups and Failover Clusters, the two highly available cases, which are two different jobs rather than one
- Version Upgrade, side-by-side and in-place, and what each one leaves you for a rollback
- Edition Change, upgrade and downgrade, which are not the same size of job in either direction
- Windows Server OS Upgrade, the move underneath SQL Server, usually driven by someone else’s deadline
- Change Management, how the work gets approved, evidenced and backed out, the wrapper around every runbook above
How They Fit Together
A real migration runs this order:
- Assess the source, Migration Risk Assessment, then Version Upgrade Readiness and Edition Feature Usage, and make the go/no-go call with evidence rather than optimism
- Generate before you touch anything, run all five generators against the source and file the output, it doubles as documentation of what the source looked like
- Migrate, follow the runbook for the shape of the move, standalone, availability group or cluster, version upgrade, edition change or Windows upgrade, restore the databases, then apply the generated scripts in order, logins before user mappings
- Validate, the login audit and post-migration validation script, then orphaned users on every database, fix what it finds
- Only then tell anyone it’s done
Patching the target to current before go-live is its own area, covered under Server and Configuration. Backup coverage on the new instance from day one is Backup and Recovery, and the security review of what you just carried across, sysadmin members, weak logins, is Security.
Best Practices Across the Series
- Run the assessment scripts before the target is built, not after, a feature or edition blocker found early changes the plan, the same blocker found late changes the date
- Script the logins first and the user mappings second, that order is the entire reason restored users find their login instead of arriving orphaned
- Keep the generated DDL rather than running it and discarding it, it is both the thing you apply to the target and the record of how the source was configured on the day
- Give validation its own slot in the plan instead of the last ten minutes of the window, a missed linked server or Agent job only surfaces when something goes looking for it
Frequently Asked Questions
Does a backup and restore move everything?
No. A restore moves the databases and the objects inside them. Logins, SQL Agent jobs, linked servers, credentials and instance-level configuration all live outside the database, so they have to be scripted across deliberately or they simply are not there on the target.
Why are users orphaned after a restore?
A database user is tied to a server login by security identifier, not by name. Restoring a database brings its users with it, but a login recreated by hand on the target gets a new SID, so the two no longer match and the user cannot connect even though both objects look right in the object explorer.
In-place upgrade or side-by-side?
It is mostly a question about rollback. Side-by-side leaves the original instance untouched, so backing out means pointing connections back at something that is still running. In-place needs no second server and is usually quicker, but the way back is a restore rather than a redirect.
Can I restore a backup onto an older version of SQL Server?
No. Backups and database files move forward only, so a database from a newer version cannot be restored or attached to an older one. That is why a version downgrade is a migration in its own right, with a fresh instance and an export path, rather than a restore in the other direction.
Should the compatibility level be raised straight after an upgrade?
Not automatically. An upgraded database keeps the compatibility level it already had, unless that level is below the minimum the new version supports, in which case setup raises it to that minimum. Going further is a deliberate change to how queries are optimised, so it belongs in a window where the workload can be tested, not in the middle of a cutover.
See Also
This pillar is part of DBA Scripts: The Complete Guide, the map across the whole series organized by the question you’re actually asking.
Summary
Migration is three phases and the scripts mirror them: assess (risk assessment, upgrade readiness, edition usage), carry (the five generators), prove (validation, orphaned users). The restore is the easy part, everything around it is where migrations succeed or fail, and every step here produces reviewable output before anything touches the target.
Start with Migration Risk Assessment if a move is on the table but not yet planned, then pick the runbook that matches the shape of the job and let the scripts fill in the steps.
Leave a Reply