Restoring a Sample Database to New Folders With RESTORE FILELISTONLY

A backup can remember folders that do not exist on your computer. RESTORE FILELISTONLY reveals the logical file names you need to restore it somewhere suitable.

Why RESTORE FILELISTONLY Comes First

A backup stores the database files' logical names and their original physical paths. The logical name identifies a file inside the restore operation. WITH MOVE gives that file a new destination on the SQL Server computer.

I read the file list before composing a restore command. I also choose a new database name before reviewing target paths. That keeps the sample copy separate from databases that already contain useful work.

A workstation folder is not automatically a server folder. SQL Server reads the backup and creates the restored files using its own service context. Confirm that both the backup location and destination folders are accessible there.

Changing the backup filename does not change its internal logical names. The file does not update its travel plans because someone renamed the suitcase. Read the metadata rather than guessing from the visible filename.

The examples use ordinary Windows paths. Create the backup directory and grant the service account appropriate access before using them. Replace the example backup path with the actual server-accessible file you intend to restore.

Build an Optional Practice Backup

If you have no sample backup, create a disposable source database for this exercise. Run this setup only on a test instance where the chosen name is unused. It creates a database and writes a real backup file.

USE master;
IF DB_ID(N'SampleRestoreSource') IS NOT NULL
    THROW 50001, 'Choose an unused practice source database name.', 1;
CREATE DATABASE SampleRestoreSource;
GO
USE SampleRestoreSource;
CREATE TABLE dbo.RestorePractice
(
    ItemID int NOT NULL CONSTRAINT PK_RestorePractice PRIMARY KEY,
    ItemText nvarchar(100) NOT NULL
);
INSERT dbo.RestorePractice(ItemID, ItemText)
VALUES (1, N'Sample row for restore verification');
BACKUP DATABASE SampleRestoreSource
TO DISK = N'D:\SqlBackups\SampleRestoreSource.bak'
WITH COPY_ONLY, CHECKSUM;
GO
USE master;

Skip that setup when you already have the intended backup. The later inspection works with either file after you set the correct path. Do not create a practice database on a production instance for convenience.

COPY_ONLY keeps this practice full backup separate from a regular differential base. CHECKSUM adds backup integrity checking. Neither option supplies a retention policy or substitutes for verifying the resulting restore.

A downloaded backup also needs a trusted origin. Restoring a database introduces its stored objects and contents into the test environment. Inspect unfamiliar databases in an appropriately isolated instance with controlled access.

Inspect Every File With RESTORE FILELISTONLY

Run the file list against the selected backup set. Its LogicalName column supplies the names for MOVE clauses, and PhysicalName shows the original locations. Type distinguishes ordinary data and log files from special containers.

RESTORE FILELISTONLY
FROM DISK = N'D:\SqlBackups\SampleRestoreSource.bak'
WITH FILE = 1;
SELECT SERVERPROPERTY('InstanceDefaultDataPath') AS DefaultDataPath,
       SERVERPROPERTY('InstanceDefaultLogPath') AS DefaultLogPath;

FILE selects the backup set position within the media file. If several backup sets exist, inspect the header first and choose the intended position. Use that same position for both the file list and the restore.

A database can have multiple data files, multiple log files, or special containers. Map every file that needs a new location. Moving only the first data file leaves the remaining original paths unresolved.

The server properties report the instance's configured default folders. They are useful starting points rather than proof of available capacity. Check the actual folders and intended storage policy before choosing them.

RESTORE FILELISTONLY also reports allocated file sizes. Plan destination capacity from the database files, not merely the compressed backup size. A small backup file can restore into much larger allocated files.

Compose the Separate Restore

The following command assumes the practice backup's ordinary data and log file names. Confirm those names against your file list before executing it. For another backup, replace them and add every required MOVE clause.

The script refuses an existing destination database. It also refuses missing default-folder values rather than manufacturing a path. Keep REPLACE out of this sample copy operation so an existing database is not deliberately overwritten.

USE master;
IF DB_ID(N'SampleRestoreCopy') IS NOT NULL
    THROW 50002, 'The destination database name is already in use.', 1;
DECLARE @DataFolder nvarchar(260) =
    CONVERT(nvarchar(260), SERVERPROPERTY('InstanceDefaultDataPath'));
DECLARE @LogFolder nvarchar(260) =
    CONVERT(nvarchar(260), SERVERPROPERTY('InstanceDefaultLogPath'));
IF @DataFolder IS NULL OR @LogFolder IS NULL
    THROW 50003, 'Choose verified destination folders explicitly.', 1;
IF RIGHT(@DataFolder, 1) <> N'\' SET @DataFolder += N'\';
IF RIGHT(@LogFolder, 1) <> N'\' SET @LogFolder += N'\';
DECLARE @DataFile nvarchar(300) = @DataFolder + N'SampleRestoreCopy.mdf';
DECLARE @LogFile nvarchar(300) = @LogFolder + N'SampleRestoreCopy_log.ldf';
DECLARE @RestoreSql nvarchar(max) =
    N'RESTORE DATABASE [SampleRestoreCopy] '
    + N'FROM DISK = N''D:\SqlBackups\SampleRestoreSource.bak'' '
    + N'WITH FILE = 1, MOVE N''SampleRestoreSource'' TO N'''
    + REPLACE(@DataFile, N'''', N'''''')
    + N''', MOVE N''SampleRestoreSource_log'' TO N'''
    + REPLACE(@LogFile, N'''', N'''''')
    + N''', STATS = 10, RECOVERY;';
SELECT @RestoreSql AS ReviewedRestoreCommand;
EXEC sys.sp_executesql @RestoreSql;

Review target filenames for collisions with existing files before running the entire block. A distinct database name alone does not establish a distinct physical location. SQL Server checks restore conflicts, but your review should identify them first.

STATS reports progress as the operation proceeds. Its requested percentage is not a promised runtime or exact progress schedule. RECOVERY brings this full restore online when no further backup application is intended.

If you need additional differential or log backups, use NORECOVERY until the final restore step. Recovering early ends that restore sequence. Choose the intended recovery path before composing the command.

Compare Restored Files With RESTORE FILELISTONLY

After successful restore, inspect sys.master_files for the destination database. Check the data and log paths against the reviewed mapping. Then query the restored fixture to confirm the expected object is accessible.

SELECT DB_NAME(database_id) AS DatabaseName,
       name AS LogicalFileName, type_desc, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(N'SampleRestoreCopy');
SELECT ItemID, ItemText
FROM SampleRestoreCopy.dbo.RestorePractice;

RESTORE FILELISTONLY supplied the source identities, while this query reports the resulting destination files. Keep both outputs with the approved restore command. That provides a clear record of the mapping used.

If a restore fails, keep the complete error message and reviewed command together. Identify whether the failure concerns a logical name, destination path, permission, or backup prerequisite. Correct that specific condition before retrying.

Do not respond to a path error by adding REPLACE. That option changes overwrite safeguards rather than creating a suitable folder. The missing path still needs a valid location and service access.

Respect Version and Feature Limits

A backup from a newer SQL Server version cannot be restored to an older engine. Changing compatibility level does not change the backup's storage version. Choose an engine capable of restoring the source version.

Encrypted backups or databases also require their appropriate keys and certificates. Special file containers need suitable destination handling. A correct ordinary MOVE clause does not resolve those separate prerequisites.

Finish With a Verified Copy

Does every file in the inspected backup have an appropriate destination? Answer that before retrying a path-related failure. Repeating the same command cannot create a missing folder or grant service access.

Keep the sample copy's database name and paths distinct throughout the exercise. Review its ownership and any application access after restoration. A restored database is a new operational object, not merely an extracted archive.

Finish by checking the destination file list and the intended data. Preserve the source backup and the reviewed mapping for later work. A successful sample restore demonstrates the restore path you verified.

Related reading on this blog: Full, Differential and Log Backups: A Practical Guide and Monitor Estimated Completion Times for Backup, Restore and DBCC Commands.

A restore path is not a guess from a filename, it is a mapping from inspected backup metadata.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.