DBA Scripts: SQL Server Migration Script Generators

Part of the DBA-Tools Project.

Moving a SQL Server workload to a new instance means recreating five things by hand, unless you script them: logins (with the same SIDs, or every restored database ends up with orphaned users), SQL Agent jobs, database user and role mappings, linked servers, and the actual RESTORE ... WITH MOVE statements when the target’s drive layout doesn’t match the source. Doing this by hand across more than a handful of databases is exactly the kind of repetitive, easy-to-miss-a-step work that migrations don’t forgive.

Five generator scripts, each covered on its own page below, each read the source instance’s own system catalogs and produce plain DDL text, reviewed in your own SSMS window, then run against the target once you’re satisfied it’s correct. Nothing touches the target automatically, that review step is the whole design. Two genuine bugs and one silent-corruption risk turned up testing these against a real SQL Server 2025 instance, all fixed and documented on the page for the script they affect.

The Five Generators

Why This Matters

• Manual migration steps are where consistency dies. Recreating twenty logins by hand across a maintenance window is exactly when one gets missed, or a permission gets applied differently than the one before it
• SID-preserving login recreation avoids the classic orphaned-user problem. A CREATE USER mapped to a login whose SID doesn’t match the original database user’s SID silently fails to map, and you find out when an application can’t connect
• Generated DDL means you review the exact statement before it runs, not a black-box migration tool doing it for you. Every sp_add_job, CREATE USER, and sp_addlinkedserver call is right there to read
• These are generators, not a full migration platform. They don’t move data, don’t orchestrate cutover timing, and don’t replace a proper migration runbook. They solve the specific, tedious sub-problem of “recreate this metadata correctly on the target”

How They Fit Together

Run these in the natural order:

1. Generate Login Script first, later steps need logins to already exist on the target
2. Restore the databases with Generate Restore With Move Script if drive layouts differ, or a plain restore if they match
3. Generate User Mapping Script next, user mapping needs both the databases and the logins to exist
4. Generate Agent Job Script and Generate Linked Server Script last, in either order, both depend on logins existing but not on each other

Beyond the Scripts: The Topology You’re Migrating Into Changes Everything

These five scripts solve one part of a migration: recreating logins, jobs, user mappings, linked servers, and file paths correctly on the target. They don’t solve the bigger question underneath all of it, how the source and target relate to each other during cutover, and that answer depends entirely on what you’re migrating from and to.

Standalone to standalone. The simplest case, and what these scripts are written for. Full backup, restore on the new server (Generate Restore With Move Script handles the path translation), run the other four scripts, then cut over. The one thing worth doing properly here even in the simple case: point applications at a DNS name, not a hardcoded server name, from the start. A CNAME means cutover is repointing one DNS record instead of touching every connection string in every application, and the next migration is just as easy.

Windows Server Failover Clustering, Always On Availability Groups, or SAN-based storage. This is a different problem, and these five scripts are a small part of it, not the whole plan. The target needs the same HA topology as the source, or a deliberate decision to change it, not just the databases and metadata recreated on a lone server. Enterprise Edition features (Always On, TDE, table partitioning, Resource Governor) need licensing and edition parity on the target before anything else matters. SAN-backed storage means the storage team is part of the migration, not just the DBA. None of that shows up in a RESTORE ... WITH MOVE script, and treating it as if it does is how a migration plan looks complete on paper and falls over on cutover night.

Migrating to Azure. A different migration again, not a variation on the same theme. Azure SQL Database, Azure SQL Managed Instance, and SQL Server on an Azure VM are three different targets with three different migration paths (the Database Migration Service, native backup-to-URL restore, or a straightforward lift-and-shift, depending which one), and the right choice depends on compatibility requirements these scripts don’t check for at all.

The honest scope of this pillar: it handles the metadata recreation problem well, for a standalone-to-standalone migration. Anything involving HA, SAN, or cloud needs that same rigor applied to a much bigger set of questions, that’s a genuinely separate piece of work, not a checklist item to add to this one.

Best Practices Across the Series

• Always review the generated DDL before running it on the target. Nothing here executes automatically, that review step is the entire point of generating text instead of just running a migration tool.
• Map owner_login_name values in the Agent Job script’s output to real logins that exist on the target before running it.
• Replace every ENTER_PASSWORD_HERE placeholder in the Linked Server script’s output; stored remote credentials genuinely cannot be scripted, there’s no way around re-entering them by hand.
• Before trusting a RESTORE ... WITH MOVE script’s paths, check for the -- WARNING: source path does not start with @OldDataRoot comment. If it’s there, the computed MOVE path is not reliable and needs to be corrected by hand.
• After restoring, run Fix-OrphanedUsers.sql (a planned addition to this set) to catch anything the SID-preserving login script didn’t line up cleanly.

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

Reading generated DDL and running generated DDL are two different claims. Two real bugs came out of actually running these five generators: a linked-server query against columns that don’t exist on sys.linked_logins, and a user-mapping generator that produced a guaranteed syntax error the moment any database had an unmapped user, which on a real instance is not a rare case. Both are fixed and reverified. A third finding, silent path corruption in the restore-with-move script when the configured path prefix doesn’t match reality, is fixed with an explicit warning rather than a silent wrong answer.

None of these five scripts replace a proper migration runbook or a dedicated migration tool. What they do is turn five tedious, error-prone manual recreation steps into DDL you can actually read before you run it. Run them in order, logins first, then restore, then user mappings, then jobs and linked servers, and run Get Migration Risk Assessment before and after to confirm the migration actually landed the way it was supposed to.