DBA Scripts: Generate Migration Scripts

Part of the DBA-Tools Project.

Generating a Migration Script Set You Can Review Before Running

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.

These five generator scripts 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 turned up testing these against a real SQL Server 2025 instance instead of just reading them end to end. Both are fixed below, in the scripts and in this post.

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.”

When to Run These Scripts

Migrating databases to new hardware or a new SQL Server version
Consolidating several instances onto one server
Standing up a DR or failover target that needs the same logins, jobs, and user mappings as production
Building a side-by-side test environment before a cutover, so application connectivity can be verified ahead of time

The Five Scripts

Each script queries the source instance’s system catalogs and returns a single NVARCHAR(MAX) DDL string (SELECT @ddl), reviewed before you run it on the target. None of them write anything themselves.

Generate-LoginScript.sql: Logins with SIDs and hashed passwords preserved

Produces CREATE LOGIN DDL for every non-system SQL and Windows login, including the SQL logins’ password hashes and original SIDs, plus server role memberships. Preserving the SID is what prevents orphaned database users after the databases are restored.

/*
Script Name : Generate-LoginScript
Category    : migration
Purpose     : Generate CREATE LOGIN DDL for all non-system logins with SIDs and hashed passwords preserved.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-generate-migration-scripts/)
Requires    : VIEW SERVER STATE, CONTROL SERVER (for password_hash column)
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

DECLARE @ddl  NVARCHAR(MAX) = N'';
DECLARE @crlf NCHAR(2)      = CHAR(13) + CHAR(10);
-- ... full DDL-assembly section in the repo, link below ...
SELECT @ddl AS ddl;

(The full script is in the repo, link below.)

Generate-AgentJobScript.sql: Every SQL Agent job, step, and schedule

Produces sp_add_job / sp_add_jobstep / sp_add_schedule DDL for every job on the source instance, in the order needed to recreate it correctly on the target: job header, then steps, then start step, then schedule, then attach to (local).

/*
Script Name : Generate-AgentJobScript
Category    : migration
Purpose     : Generate sp_add_job DDL to recreate all SQL Agent jobs on the target server.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-generate-migration-scripts/)
Requires    : SQLAgentUserRole in msdb (or sysadmin)
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
-- ... per-job cursor assembling job/step/schedule DDL, full script in the repo ...
SELECT @ddl AS ddl;

Generate-UserMappingScript.sql: Database users, roles, and role memberships

Loops over every online, writable user database and produces, in order: ALTER AUTHORIZATION to re-map the database owner, CREATE ROLE for custom roles, CREATE USER for each database user (matched back to a server login where possible), and ALTER ROLE ADD MEMBER for role memberships.

/*
Script Name : Generate-UserMappingScript
Category    : migration
Purpose     : Generate CREATE USER and role membership DDL for all user databases.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-generate-migration-scripts/)
Requires    : VIEW ANY DATABASE, VIEW DEFINITION on each database
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
-- ... per-database dynamic SQL assembling owner/role/user/membership DDL ...
SELECT @ddl AS ddl;

Generate-LinkedServerScript.sql: Linked servers and login mappings

Produces sp_addlinkedserver and sp_addlinkedsrvlogin DDL for every linked server. Stored remote credentials can’t be scripted (SQL Server doesn’t expose them), so those mappings come out with an ENTER_PASSWORD_HERE placeholder and a clear comment; impersonation mappings (useself = 1) need no manual entry.

/*
Script Name : Generate-LinkedServerScript
Category    : migration
Purpose     : Generate sp_addlinkedserver + sp_addlinkedsrvlogin DDL for all linked servers.
              Run on SOURCE server. Execute the output on TARGET after migration.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-generate-migration-scripts/)
Requires    : VIEW ANY DEFINITION or sysadmin
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
-- ... per-linked-server cursor, per-login-mapping nested cursor ...
SELECT @ddl AS ddl;

Generate-RestoreWithMoveScript.sql: RESTORE DATABASE with WITH MOVE

Reads the source server’s file layout from sys.master_files and produces RESTORE DATABASE ... WITH MOVE statements for every online user database, substituting an old data/log root path prefix for a new one. Use this one specifically when source and target have different drive layouts; if the layout is identical, Generate-RestoreScript.sql needs no WITH MOVE clauses at all.

/*
Script Name : Generate-RestoreWithMoveScript
Category    : migration
Purpose     : Generate RESTORE DATABASE scripts with WITH MOVE for all online user databases.
              Run on SOURCE server. Supply the backup path and path prefix mappings for
              data and log files before executing the output on TARGET.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-generate-migration-scripts/)
Requires    : VIEW ANY DATABASE, VIEW SERVER STATE
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

-- ── Adjust these four variables ───────────────────────────────────────────────
DECLARE @BackupPath    nvarchar(260) = N'\\BACKUP-SERVER\SQL-Backups';
DECLARE @OldDataRoot   nvarchar(260) = N'E:\SQLData';
DECLARE @NewDataRoot   nvarchar(260) = N'D:\SQLData';
DECLARE @OldLogRoot    nvarchar(260) = N'L:\SQLLogs';
DECLARE @NewLogRoot    nvarchar(260) = N'L:\SQLLogs';
-- ─────────────────────────────────────────────────────────────────────────────

After running the output on the target, the script’s own header notes point to two follow-ups: verify every database came back ONLINE, then run Fix-OrphanedUsers.sql to re-map any database users whose SIDs didn’t line up (not yet published, still a planned addition to this migration set).

How To Run From The Repo

Clone DBA Tools, initialize, then run each generator, review the DDL it produces, and run the output against the target instance:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Set the source server for the session:
.\tools\local-sql\Set-SqlConnection.ps1 -ServerInstance PROD01\SQL2019

# Generate each script's DDL, review the output before running it on the target:
.\powershell\migration\Generate-LoginScript.ps1
.\powershell\migration\Generate-AgentJobScript.ps1
.\powershell\migration\Generate-UserMappingScript.ps1
.\powershell\migration\Generate-LinkedServerScript.ps1
.\powershell\migration\Generate-RestoreWithMoveScript.ps1

# Output: output-files\migration\*.sql

These scripts live in the repo at:

Two Real Bugs Found Testing This Against SQL Server 2025

These five generators had never been run against a real instance before this post. Reading the DDL they produce looks correct; running it is what actually caught the problems.

Bug 1: `Generate-LinkedServerScript.sql` queried columns that don’t exist on `sys.linked_logins`

The login-mapping cursor failed immediately: Invalid column name 'local_login_name'. Invalid column name 'uses_self_credentials'. The original query assumed sys.linked_logins exposes a local_login_name column directly and a plural uses_self_credentials flag. Neither exists. The real columns are local_principal_id (an integer that has to be joined back to sys.server_principals to get a login name) and uses_self_credential, singular. Fixed by adding the join and correcting the column name:

DECLARE ll_cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT
        ISNULL(sp.name, N''),
        ISNULL(ll.remote_name, N''),
        ll.uses_self_credential
    FROM sys.linked_logins ll
    INNER JOIN sys.servers s2 ON ll.server_id = s2.server_id
    LEFT JOIN sys.server_principals sp ON ll.local_principal_id = sp.principal_id
    WHERE s2.name = @ls_name
    ORDER BY sp.name;

Verified by creating a real throwaway linked server (ZZZ_TEST_LINKED) with both an impersonation mapping and an explicit login mapping, dropping it, then re-running the fixed generator’s own output from scratch to recreate it. Queried sys.linked_logins afterward and confirmed both mappings came back exactly as configured, then dropped the test server.

Bug 2: `Generate-UserMappingScript.sql` produced a bare comment with no statement, a guaranteed syntax error the moment any database user has no matching server login

For a database user with no server-level login to map to, the generator emitted:

IF NOT EXISTS (SELECT 1 FROM [DemoDatabase].sys.database_principals WHERE name = N'DemoSeverityUser')
    -- SKIP (no matching server login): DemoSeverityUser
GO

A comment isn’t a statement. IF with nothing but a comment after it is Incorrect syntax near ')', Msg 102, confirmed by running that exact pattern directly. The bug: the generator’s CASE expression built the IF NOT EXISTS guard as a fixed prefix, then appended the CASE result (either a real CREATE USER statement or, in the unmapped case, just a bare comment) underneath it, unconditionally, on every branch. Unmapped users are common in a real migration (a login got renamed, a service account changed, an old employee’s login was already dropped), so this wasn’t a rare edge case, it broke on the very first unmapped user in a real run. Fixed by moving the IF NOT EXISTS guard inside the three branches that actually need it, and letting the unmapped case stand alone as a plain comment with no guard at all:

SELECT @chunk = @chunk
    + CASE
        WHEN utype IN ('U', 'G')
            THEN N'IF NOT EXISTS (...)' + @crlf + N'    CREATE USER [...] FOR LOGIN [...]' + @crlf
        WHEN utype = 'S' AND usid IS NOT NULL AND EXISTS (...)
            THEN N'IF NOT EXISTS (...)' + @crlf + N'    CREATE USER [...] FOR LOGIN [...]' + @crlf
        WHEN utype = 'S' AND auth_type = 'DATABASE'
            THEN N'IF NOT EXISTS (...)' + @crlf + N'    CREATE USER [...] WITHOUT LOGIN' + @crlf
        ELSE N'-- SKIP (no matching server login): ' + uname + @crlf
      END
    + N'GO' + @crlf + @crlf

Verified by re-running the generator against every real database on the test instance and executing the full 264-line output end to end with zero errors, where the previous version failed on the first database with an unmapped user.

A related, non-syntax finding in the same generator worth flagging rather than treating as a fourth bug: Generate-RestoreWithMoveScript.sql had a genuine double-comma (STATS = 5, immediately followed by a ,MOVE line) that broke on every single database, plus a silent-corruption risk if a database’s real file path doesn’t start with the @OldDataRoot / @OldLogRoot prefix you configured, producing a plausible-looking but wrong MOVE target with no warning at all. Testing with this box’s actual file paths (D:\MSSQL\DATA\..., not the script’s placeholder default E:\SQLData) reproduced the corruption directly: D:\SQLDataATA\DemoDatabase_Data.mdf, ten characters of the real path silently mangled into the new prefix. Both are fixed: the trailing comma is gone, and a -- WARNING: source path does not start with @OldDataRoot (...), verify manually comment now gets emitted above any MOVE line where the prefixes don’t match, naming the real source path so it can’t be missed on review.

What Got Verified, and What Didn’t

Every generator here produces DDL, not an executed change, but “the DDL looks right” and “the DDL actually runs and does the right thing” are different claims, and this post only makes the second one where it was actually checked:

Script	Verification performed
Generate-LoginScript	Ran for real (149 lines, 9 real logins with hashed passwords + SIDs + role memberships). Took the exact generated `CREATE LOGIN ... WITH PASSWORD = ... HASHED, SID = ...` syntax, created a real throwaway login from it, confirmed it in `sys.server_principals`, dropped it.
Generate-AgentJobScript	Ran for real (895 lines, every real job on the instance, including the disabled maintenance jobs from an earlier post). Took one job’s generated block, created a real test job with a step and an attached schedule, confirmed all three in `msdb`, then deleted it.
Generate-UserMappingScript	Ran for real against every online database on the instance (264 lines). Found and fixed Bug 2, then re-ran the full script end to end with zero errors.
Generate-LinkedServerScript	Found and fixed Bug 1 using a real throwaway linked server with two login mappings, verified with a genuine drop-and-recreate-from-generated-DDL round trip.
Generate-RestoreWithMoveScript	Generated DDL for real, fixed the double-comma and silent-path-corruption issues, confirmed the corrected DDL is syntactically valid with `SET NOEXEC ON` (parses and resolves object names without executing). A full physical restore was deliberately not run: even the smallest test database’s backup file is under 1MB, but `RESTORE` pre-allocates the full data and log file sizes on disk regardless of how little data they actually hold, over 2.5GB combined against well under a gigabyte of free disk on the test box. Running it would have failed on disk space, not proven anything about the script.

Best Practices

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.
Run these in the natural order: logins first, then restore the databases, then user mappings (they need both the databases and the logins to exist), then jobs and linked servers.
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.

Related Scripts

You may also find these scripts useful:

Get Migration Risk Assessment
Migration Login Audit and Post-Migration Validation
Fix Orphaned Users

Summary

Reading generated DDL and running generated DDL are two different claims, and this post is honest about which of those happened for each of the five scripts here. Two real bugs came out of actually running them: 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, which is the same bar every script in this repo is held to. Review what they generate, map the values that need mapping, and run Get Migration Risk Assessment before and after to confirm the migration actually landed the way it was supposed to.

Blog Categories

Comments

Leave a Reply Cancel reply