Part of the DBA-Tools Project.
Generating RESTORE … WITH MOVE for a Migration With a Different Drive Layout
Moving a SQL Server workload to a new instance where the target’s drive layout doesn’t match the source means every RESTORE DATABASE needs a WITH MOVE clause per file, unless you script it. This script 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. Reviewed in your own SSMS window, then run against the target once you’re satisfied it’s correct.
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.
Real, reproducible findings turned up testing this against a real SQL Server 2025 instance instead of just reading it end to end. Both are fixed below, in the script and in this post, one is a syntax bug, the other is a silent-corruption risk that’s more serious.
Why a Generated Restore-With-Move Script Matters
- A
WITH MOVEclause per file, across every database, is exactly the kind of repetitive work a single typo can quietly corrupt - Generated DDL means you review the exact target paths before running anything, catching a wrong path before it becomes a restored database in the wrong place
- This is a generator, not a full migration platform. It doesn’t move data or orchestrate cutover, it solves the specific problem of computing correct restore paths on a target with a different drive layout
When to Run This Script
- Migrating to a target where the data/log drive letters or root paths differ from the source
- Consolidating several instances onto one server with a standardized drive layout
- Standing up a DR or failover target on hardware with a different storage configuration than production
The Script
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.
/*
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-restore-with-move-script/)
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).
(The full script is in the repo, link below.)
How To Run From The Repo
# 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 the DDL, review the output before running it on the target:
.\powershell\migration\Generate-RestoreWithMoveScript.ps1
# Output: output-files\migration\*.sql
This script lives in the repo at:
The Findings: A Syntax Bug, and a Silent-Corruption Risk
The syntax bug: a genuine double-comma (STATS = 5, immediately followed by a ,MOVE line) broke on every single database.
The more serious finding, a silent-corruption risk: if a database’s real file path doesn’t start with the @OldDataRoot / @OldLogRoot prefix you configured, the script produced 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
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 at the time. Running it would have failed on disk space, not proven anything about the script.
Understanding the Results
- A
-- WARNING: source path does not start with @OldDataRootcomment in the output means don’t trust thatMOVEline, the computed path is not reliable and needs to be corrected by hand before running it - No warning comments means every file’s real path matched the configured prefix, the substitution is mechanically reliable in that case
SET NOEXEC ONvalidation confirms syntax and object references, not that a physical restore will succeed, disk space, permissions, and the actual backup file’s validity still need checking separately
Best Practices
- Always review the generated DDL before running it on the target. Nothing here executes automatically.
- Before trusting any
MOVEpath, check for the-- WARNING: source path does not start with @OldDataRootcomment. If it’s there, correct the path by hand. - Confirm the actual source file paths with Database Files Detail before setting
@OldDataRoot/@OldLogRoot, don’t guess or reuse a value from a different server. - Verify every database came back
ONLINEafter running the restore output on the target, don’t assume success just because the job completed.
Related Scripts
You may also find these scripts useful:
- SQL Server Migration Script Generators (hub)
- Generate User Mapping Script
- Database Files Detail
- Get Migration Risk Assessment
Frequently Asked Questions
What does the “source path does not start with @OldDataRoot” warning mean?
Your configured @OldDataRoot or @OldLogRoot doesn’t match what a database’s file path actually starts with, so the string substitution that builds the new MOVE path can’t be trusted for that file. Check the real path named in the warning and correct the MOVE target by hand.
Why wasn’t a full physical restore tested?
The test instance’s smallest backup was under 1MB, but RESTORE pre-allocates full data and log file sizes regardless, over 2.5GB combined against well under a gigabyte of free disk at the time. SET NOEXEC ON validated the generated syntax and object references instead, which is what was actually achievable without failing on disk space rather than proving anything about the script.
Summary
One generator, one job: compute correct RESTORE ... WITH MOVE paths for every database when target and source drive layouts differ. Two real findings from testing this against a live instance, a double-comma syntax bug and a silent path-corruption risk, are both fixed, the second with an explicit warning rather than a silently wrong answer. Check for warning comments in the output before trusting any MOVE path.
Leave a Reply