Proving the Restore, Part 4: Finding Backup Files Without xp_cmdshell

2026-08-26 · by Hannah Vernon · in recovery

Everything so far assumed you already knew the path to a backup file. Automating any of it means the code has to find the files itself, and SQL Server gives you no documented way to list a directory.

A woman archivist holds a glowing lantern up to a wall of labelled filing cabinets in a dim archive room, with a heavy bolted door closed behind her.

Start with what msdb knows

Before reaching for anything exotic, msdb records the path of every backup this instance wrote:^[1]

SELECT
      [b].[database_name]
    , [b].[type]
    , [b].[backup_finish_date]
    , [b].[position]
    , [m].[physical_device_name]
    , [m].[family_sequence_number]
FROM
    [msdb].[dbo].[backupset] AS [b]
    INNER JOIN [msdb].[dbo].[backupmediafamily] AS [m]
        ON [m].[media_set_id] = [b].[media_set_id]
WHERE
    [b].[database_name] = N'Sales'
    AND [b].[backup_finish_date] >= DATEADD(DAY, -14, SYSDATETIME())
ORDER BY
    [b].[backup_finish_date] DESC
  , [m].[family_sequence_number];

SELECT

[b].[database_name]

, [b].[type]

, [b].[backup_finish_date]

, [b].[position]

, [m].[physical_device_name]

, [m].[family_sequence_number]

FROM

[msdb].[dbo].[backupset] AS [b]

INNER JOIN [msdb].[dbo].[backupmediafamily] AS [m]

ON [m].[media_set_id] = [b].[media_set_id]

WHERE

[b].[database_name] = N'Sales'

AND [b].[backup_finish_date] >= DATEADD(DAY, -14, SYSDATETIME())

ORDER BY

[b].[backup_finish_date] DESC

, [m].[family_sequence_number];

family_sequence_number is how striped backups appear here, one row per stripe, and part 5 is about those.

For a single instance that has been running continuously, this is the best answer. It costs nothing, needs no extra permissions, and gives you paths and positions together.

It breaks in the cases that matter most. msdb doesn’t know about backups taken before a migration onto this hardware. It doesn’t know about backups taken on the other replica of an availability group. It’s confidently wrong when files have been deleted, moved, or archived, because deleting a file doesn’t touch the row. After a migration or a failover, the instance you’re working on may hold the least complete history on the estate.

So at some point you need to look at the filesystem.

The options, honestly

xp_cmdshell. It works, and it’s commonly disabled by policy. The objection isn’t that it’s an extended stored procedure, it’s that it runs arbitrary commands as the SQL Server service account by default, which makes any SQL injection reachable from the OS.^[2] Turning it on to list files is a poor trade.

OLE Automation procedures. sp_OACreate with Scripting.FileSystemObject lists directories. It’s another server-level surface area switch, it leaks object handles when error handling is imperfect, and it’s a worse choice than xp_cmdshell rather than a better one.

SQLCLR. A signed assembly with System.IO access does this cleanly and is genuinely defensible. The cost is deployment: an assembly to build, sign, version, and push through the same release process as everything else, for one directory listing.

xp_dirtree. Undocumented, and long present in SQL Server. Three parameters: path, depth, and whether to include files.

CREATE TABLE #files
(
      [subdirectory] nvarchar(512) NOT NULL
    , [depth]        int           NOT NULL
    , [is_file]      bit           NOT NULL
);

INSERT INTO #files ([subdirectory], [depth], [is_file])
EXEC [master].[dbo].[xp_dirtree] N'D:\Backups\Sales', 1, 1;

SELECT [subdirectory]
FROM   #files
WHERE  [is_file] = 1
  AND  [subdirectory] LIKE N'%.bak';

CREATE TABLE #files

(

[subdirectory] nvarchar(512) NOT NULL

, [depth] int NOT NULL

, [is_file] bit NOT NULL

);

INSERT INTO #files ([subdirectory], [depth], [is_file])

EXEC [master].[dbo].[xp_dirtree] N'D:\Backups\Sales', 1, 1;

SELECT [subdirectory]

FROM #files

WHERE [is_file] = 1

AND [subdirectory] LIKE N'%.bak';

It returns names only, not full paths, so you rebuild the path yourself. Being undocumented, it carries no compatibility promise.

sys.dm_os_enumerate_filesystem. This is the one I’d reach for now, and I want to be straight about its status: it is not documented on Microsoft Learn. Searching the SQL Server reference returns sys.dm_os_enumerate_fixed_drives, which enumerates volumes mounted to drive letters, and is a different thing entirely.^[3]

It takes a base path and a search pattern, and returns a proper result set you can join to:

SELECT
      [full_filesystem_path]
    , [file_or_directory_name]
    , [is_directory]
    , [size_in_bytes]
    , [last_write_time]
FROM
    sys.dm_os_enumerate_filesystem(N'D:\Backups\Sales', N'*.bak')
WHERE
    [is_directory] = 0
ORDER BY
    [last_write_time] DESC;

SELECT

[full_filesystem_path]

, [file_or_directory_name]

, [is_directory]

, [size_in_bytes]

, [last_write_time]

FROM

sys.dm_os_enumerate_filesystem(N'D:\Backups\Sales', N'*.bak')

WHERE

[is_directory] = 0

ORDER BY

[last_write_time] DESC;

Being a table-valued function rather than a procedure is the practical advantage. No temp table, no INSERT ... EXEC, and you can filter and join in the same statement.

Because it’s undocumented there’s no reference page to check the column list against, so confirm the names and types on your own version before writing anything that depends on them. The tradeoff is otherwise the same as xp_dirtree: unsupported means it can change or disappear in a servicing update with no notice.

Design for the enumeration failing

Whichever you pick, the enumeration is the least reliable step in the chain. It depends on the SQL Server service account having access to the path, which for a UNC share is a different identity from the one you’re logged in as. A path that opens instantly in your own Explorer window can be invisible to the engine.

So don’t let a failed listing become a failed validation. Fall back to probing.

You know your own naming convention. If backups land as Sales_FULL_20260817.bak, you can construct candidate paths for the dates you care about and ask RESTORE HEADERONLY about each one. A file that isn’t there produces an error you can catch and move past:

BEGIN TRY

    INSERT INTO #header_results
    EXEC (@restore_command);

    SET @file_exists = 1;

END TRY
BEGIN CATCH

    /* Msg 3201: cannot open the device.  Treat as "not present" and continue. */
    IF ERROR_NUMBER() IN (3201, 3013)
        SET @file_exists = 0;
    ELSE
        THROW;

END CATCH;

BEGIN TRY

INSERT INTO #header_results

EXEC (@restore_command);

SET @file_exists = 1;

END TRY

BEGIN CATCH

/* Msg 3201: cannot open the device. Treat as "not present" and continue. */

IF ERROR_NUMBER() IN (3201, 3013)

SET @file_exists = 0;

ELSE

THROW;

END CATCH;

Probing is slower than listing, because you pay a file open per candidate rather than one directory read. It tests the operation you care about, though: a file can enumerate and still fail to open under RESTORE.

Rethrowing unexpected errors matters as much as catching the expected ones. Treating every error as “file absent” can report a clean chain on an instance that has lost access to the whole share.

Where the path itself comes from

One more thing worth resolving rather than hardcoding: the backup root. SERVERPROPERTY doesn’t expose it, but the instance default lives in the registry and xp_instance_regread reads it with the instance name substituted for you:

DECLARE @backup_directory nvarchar(512);

EXEC [master].[dbo].[xp_instance_regread]
      N'HKEY_LOCAL_MACHINE'
    , N'Software\Microsoft\MSSQLServer\MSSQLServer'
    , N'BackupDirectory'
    , @backup_directory OUTPUT;

SELECT [backup_directory] = @backup_directory;

DECLARE @backup_directory nvarchar(512);

EXEC [master].[dbo].[xp_instance_regread]

N'HKEY_LOCAL_MACHINE'

, N'Software\Microsoft\MSSQLServer\MSSQLServer'

, N'BackupDirectory'

, @backup_directory OUTPUT;

SELECT [backup_directory] = @backup_directory;

xp_instance_regread is also undocumented. The documented alternative is to store the path in a configuration table you control, which is the better answer for anything that has to survive a support conversation.

With files located and headers readable, the checks can start. The first one is structural: a striped backup is written as several files that only mean something together, and finding seven of eight is the same as finding none. That’s part 5.

What do you use to enumerate backup files, and did you have to argue with anyone to get it approved? Bluesky or LinkedIn.

References

backupmediafamily – Microsoft Learn. Documents physical_device_name and family_sequence_number, the latter identifying each stripe of a striped backup set. ↩
xp_cmdshell server configuration option – Microsoft Learn. Covers the security model, including that commands run under the SQL Server service account for sysadmin callers. ↩
sys.dm_os_enumerate_fixed_drives – Microsoft Learn. The documented drive-enumeration DMV, included here to show what does exist in the reference. There is no corresponding Microsoft Learn page for sys.dm_os_enumerate_filesystem, which is the basis for calling it undocumented. ↩

Tags: backup, DMV, restore, security, xp_cmdshell

Proving the Restore, Part 4: Finding Backup Files Without xp_cmdshell

Start with what msdb knows

The options, honestly

Design for the enumeration failing

Where the path itself comes from

Next

References

Related

Let us know your thoughts on this postCancel reply

Search

Categories

Pages

Meta

Proving the Restore, Part 4: Finding Backup Files Without xp_cmdshell

Start with what msdb knows

The options, honestly

Design for the enumeration failing

Where the path itself comes from

Next

References

Share this:

Related

Let us know your thoughts on this postCancel reply

Search

Categories

Pages

Meta