MSSQL RESTORE DATABASE Hands-on: Restore to a New DB Using FILE + MOVE

Conclusion#

Using RESTORE FILELISTONLY + MOVE allows you to restore a backup file to a new database name, preventing overwriting the original DB, and also lets you re-specify the MDF / LDF paths.

Where It’s Useful#

Restoring production backups to a test environment
Duplicating a database for query analysis
Creating a test copy before a release
When the original DB cannot be touched, but data validation is needed

Steps#

1. Verify Backup File Content and Version#

First, check if the .bak file is usable and if the backup type is correct. If there are multiple backups, you can also identify which one to restore.

1
RESTORE HEADERONLY
2
FROM DISK = N'C:\SampleBackup\DemoDb_backup_2026_04_17_163005.bak';

If you see BackupType = 1, it usually indicates a full backup, which can be restored directly.

2. Query Data File Logical Names#

When restoring, MOVE uses the logical names within the backup, not the physical file names. This step is crucial; otherwise, errors often occur.

1
RESTORE FILELISTONLY
2
FROM DISK = N'C:\SampleBackup\DemoDb_backup_2026_04_17_163005.bak';
3
GO

You will see something similar to:

LogicalName	Type
DemoDb	D
DemoDb_log	L

These two names will then be used with MOVE.

3. Restore to a New Database Name#

Create a new database DemoDb_Copy260417 and place the data files in a new path to avoid conflicts with the original DB.

1
RESTORE DATABASE [DemoDb_Copy260417]
2
FROM DISK = N'C:\SampleBackup\DemoDb_backup_2026_04_17_163005.bak'
3
WITH
4
    FILE = 1,
5
    MOVE N'DemoDb' TO N'D:\MSSQL\Data\DemoDb_Copy260417.mdf',
6
    MOVE N'DemoDb_log' TO N'D:\MSSQL\Log\DemoDb_Copy260417.ldf',
7
    RECOVERY,
8
    STATS = 10;
9
GO

STATS = 10 means progress will be displayed every 10%. Just as people appreciate updates, so does SQL.

Additional Notes#

FILE = 1 typically refers to the first backup set; if a single .bak file contains multiple backups, you need to use HEADERONLY first to confirm the Position.
If the MDF / LDF paths do not exist, the restore will fail. Create the folders first.
If the target DB already exists, you can delete it first or add REPLACE (use with caution in production environments).

Command / Example Summary#

Click to expand

1
RESTORE HEADERONLY
2
FROM DISK = N'C:\SampleBackup\DemoDb_backup_2026_04_17_163005.bak';
3

4
RESTORE FILELISTONLY
5
FROM DISK = N'C:\SampleBackup\DemoDb_backup_2026_04_17_163005.bak';
6
GO
7

8
RESTORE DATABASE [DemoDb_Copy260417]
9
FROM DISK = N'C:\SampleBackup\DemoDb_backup_2026_04_17_163005.bak'
10
WITH
11
    FILE = 1,
12
    MOVE N'DemoDb' TO N'D:\MSSQL\Data\DemoDb_Copy260417.mdf',
13
    MOVE N'DemoDb_log' TO N'D:\MSSQL\Log\DemoDb_Copy260417.ldf',
14
    RECOVERY,
15
    STATS = 10;
16
GO

Wrap-up#

Restoring a database isn’t difficult; the challenge lies in getting the logical file names and paths wrong. Clarify them before proceeding to avoid many pitfalls.