calendar Jan 1, 0001 · 8 min read  ·
Share on: linkedin copy

sql-server-restore-database-script-with-move-option.md

title: "SQL Server Restore Database Script with MOVE" description: "Use dynamic SQL with the MOVE option to restore a SQL Server database and remap data and log file paths to any target directory on the server." keywords:

  • sql server restore database
  • restore with move
  • remap file paths
  • dynamic sql restore
  • RESTORE DATABASE
  • RESTORE FILELISTONLY
  • backup restore script
  • sql server database recovery
  • sp_executesql restore tags:
  • restore database
  • backup recovery
  • dynamic sql
  • sql server
  • RESTORE DATABASE
  • RESTORE FILELISTONLY
  • database administration
  • scripts date: 2026-04-09 lastmod: 2026-04-11 showLastmod: true categories:
  • Scripts
  • SQL Server Backup Recovery series:
  • admin-guide featured: false

Restore a SQL Server Database with File Path Remapping

Restoring a SQL Server database to a different server or folder often fails because the file paths in the backup do not match the target environment. The MOVE option in the RESTORE DATABASE statement lets you specify exactly where each data and log file should land.

Purpose and Overview

When you restore a backup to a server where the original file paths do not exist, SQL Server returns an error and the restore does not complete. The MOVE option solves this by letting you redirect each logical file in the backup to a physical path that exists on the target system.

This script wraps the RESTORE DATABASE statement in dynamic SQL so that the database name, backup file path, and target folder are all set through variables at the top. A debug flag lets you preview the generated statement before executing it, which reduces the risk of running a restore with incorrect parameters.

The script is sourced from the sqlserver-kit repository by Andrey Zavadskiy and is a practical starting point for any environment where file paths differ between source and target.

Code Breakdown

The complete script is shown below, followed by an explanation of each section.

 1USE master;
 2GO
 3
 4DECLARE @restoreStatement NVARCHAR(4000) = N'';
 5DECLARE @databaseName     SYSNAME        = N'YourDatabaseName';      -- Set your database name
 6DECLARE @backupFilePath   NVARCHAR(2000) = N'C:\Backups\YourDatabaseName.bak'; -- Set your backup file path
 7DECLARE @serverPath       NVARCHAR(2000) = N'C:\Program Files\Microsoft SQL Server\MSSQL\DATA\'; -- Set target data folder
 8DECLARE @debug            BIT = 1; -- Set to 0 to execute, 1 to preview only
 9
10SET @restoreStatement = N'
11RESTORE DATABASE [@databaseName] FROM DISK = N''@backupFilePath''
12WITH  FILE = 1,
13      MOVE N''@databaseName_Data'' TO N''@serverPath@databaseName_Data.mdf'',
14      MOVE N''@databaseName_Log''  TO N''@serverPath@databaseName_Log.ldf'',
15      NOUNLOAD,
16      STATS = 5';
17
18SET @restoreStatement = REPLACE(@restoreStatement, '@databaseName',   @databaseName);
19SET @restoreStatement = REPLACE(@restoreStatement, '@backupFilePath', @backupFilePath);
20SET @restoreStatement = REPLACE(@restoreStatement, '@serverPath',     @serverPath);
21
22IF @debug = 1 PRINT(@restoreStatement)
23ELSE EXEC sp_executesql @restoreStatement;
24GO

USE master

The script runs in the master database context. Restore operations must be issued from a database other than the one being restored, and master is the standard choice for administrative work.

Variable Declarations

Four variables control all the moving parts of the restore:

  • @databaseName — the name of the database to restore. This value is substituted into both the database name and the logical file name placeholders.
  • @backupFilePath — the full path to the .bak file on disk.
  • @serverPath — the target folder where the data and log files will be written. This must be a folder that already exists and is accessible to the SQL Server service account.
  • @debug — when set to 1, the script prints the generated statement without executing it. Set to 0 to run the restore.

Building the Restore Statement

The restore statement is stored as a string in @restoreStatement. It uses placeholder tokens (@databaseName, @backupFilePath, @serverPath) rather than hard-coded values. This keeps the template readable and easy to adjust.

The MOVE clause appears twice — once for the data file (.mdf) and once for the log file (.ldf). The logical file names follow the common SQL Server convention of DatabaseName_Data and DatabaseName_Log. If your backup uses different logical names, update these values after running RESTORE FILELISTONLY (see the Practical Tips section below).

STATS = 5 prints a progress message every 5 percent of the restore, which is useful for monitoring long-running restores.

Token Replacement

Three REPLACE calls substitute the actual values into the template string. The replacements are applied in sequence: database name first, then backup path, then server path. Because @databaseName appears multiple times in the template (in the database name and in the logical file name tokens), all occurrences are replaced in a single call.

Debug Flag and Execution

The final IF block checks the @debug flag. When debug is on, PRINT outputs the fully substituted statement to the Messages tab so you can review it before committing. When debug is off, sp_executesql executes the statement. Using sp_executesql is preferred over EXEC() for dynamic SQL because it supports parameterization, though for a restore statement with no user-supplied input the practical difference is minimal.

Key Benefits and Use Cases

Cross-server restores — When you restore a backup from a production server to a development or staging server, the original file paths usually do not exist. The MOVE option lets you direct the files to whatever path is available on the target.

Non-default SQL Server installations — On servers where SQL Server was installed to a non-standard drive or folder, any backup that references the default C:\Program Files\Microsoft SQL Server\MSSQL\DATA\ path will fail without MOVE.

Consolidating files onto a specific drive — In environments where data files and log files are separated across drives for I/O performance, the MOVE option lets you place each file exactly where it needs to go.

Automation and scripting — Because the script uses variables and dynamic SQL, it can be adapted for use in SQL Server Agent jobs, PowerShell wrappers, or deployment scripts where database names and paths are passed in at runtime.

Performance Considerations

  • STATS interval — The script uses STATS = 5, which prints progress every 5 percent. In automated jobs where output is not monitored, increase this value (for example, STATS = 10) to reduce the volume of messages written to the job history log.

  • Target drive speed — The restore speed is largely determined by the speed of the disk where the backup file is read and the disk where the restored files are written. Placing the .bak file on a fast local drive and writing to an SSD significantly reduces restore time compared to reading from a network share.

  • File pre-allocation — SQL Server allocates the full size of the data file during restore. Ensure the target volume has enough free space before starting. A restore that runs out of disk space mid-way will fail and may leave a partial database in a recovering state.

  • Recovery model — After a restore with RECOVERY (the default), the database is brought online immediately. If you plan to apply additional transaction log backups after the full restore, use WITH NORECOVERY to leave the database in a restoring state.

Practical Tips

Always run RESTORE FILELISTONLY first. The logical file names used in the MOVE clause must match what is recorded in the backup. The default naming convention (DatabaseName_Data, DatabaseName_Log) holds for most databases created with standard tools, but databases created by third-party applications often use different names. Use this query to check before running the restore:

1-- Step 1: Discover logical file names from the backup file
2RESTORE FILELISTONLY
3FROM DISK = N'C:\Backups\YourDatabaseName.bak';

The result set includes a LogicalName column for each file in the backup. Copy those names into the MOVE clauses in the restore script.

Test with @debug = 1 first. Always run the script in preview mode and read the output before setting @debug = 0. This catches typos in paths and confirms the substitutions look correct before SQL Server touches any files.

Verify the target folder exists. SQL Server does not create missing folders during a restore. If the path specified in @serverPath does not exist, the restore fails with a file not found error. Create the folder in advance and confirm the SQL Server service account has write permission to it.

Handle multiple data files. Databases with more than one data file (for example, a primary .mdf and one or more .ndf secondary files) require a MOVE clause for every file. Run RESTORE FILELISTONLY and add a MOVE line for each row returned.

Adjust for named instances. On a named instance, the default data folder typically includes the instance name in the path, such as C:\Program Files\Microsoft SQL Server\MSSQL16.INSTANCENAME\MSSQL\DATA\. Update @serverPath accordingly.

Conclusion

The MOVE option is a straightforward way to handle the file path mismatch that makes cross-server and cross-environment restores fail. By wrapping the restore statement in dynamic SQL with a debug flag, this script gives you a safe, repeatable process: set your variables, preview the output, then execute when you are confident the paths are correct. Running RESTORE FILELISTONLY before the restore ensures the logical file names match, which is the most common source of errors in restore operations.

References