Parsing File Name Dates to Find the Newest Backup With REGEXP_SUBSTR

File name dates can show you the newest backup, but only after you parse them and reject the fake ones. SQL Server 2025 has REGEXP_SUBSTR, which pulls a date out of a name in one line. Then TRY_CONVERT decides if it is a date at all.

Start with a list of file names

Imagine a restore at 2 AM. Your script lists the backup folder and picks the file that sorts last. Sadly, the last one is a copy someone made, or a log backup, or a file with a typo in the date. You restore the wrong thing and lose an hour.

Sorting names as text is not the same as sorting dates. Our demo has five names for one database. They mix dashed dates, compact dates, a copy, an impossible date, and a log backup. The list sits in a temp table, so the demo does not read your disk. I keep the database name in its own column. Guessing it from an underscore breaks when a database name has an underscore in it.

DROP TABLE IF EXISTS #Files;

CREATE TABLE #Files (Id int PRIMARY KEY, DatabaseName sysname, FileName nvarchar(260));
INSERT #Files VALUES
    (1, N'Sales', N'Sales_2026-03-15.bak'),
    (2, N'Sales', N'Sales_20260316.bak'),
    (3, N'Sales', N'Sales_2026-02-30.bak'),
    (4, N'Sales', N'Sales_20260316_copy.bak'),
    (5, N'Sales', N'Sales_20260401.trn');

Pull the date out of the name

Two naming styles are in use here, so we try two patterns. The first looks for a dashed date, like 2026-03-15. The second looks for eight digits in a row, like 20260316. REGEXP_SUBSTR returns the matching text, or NULL when there is no match.

A pattern only recognizes a shape. It cannot tell if the date exists. February 30 has the right shape and is still not a day. That is why the next step matters.

Convert with a deliberate style

TRY_CONVERT with style 23 reads the dashed form. Style 112 reads the compact form. When the text is not a real date, TRY_CONVERT returns NULL instead of raising an error. COALESCE then takes whichever style worked.

Run this query first. It keeps every row, so you can see what was rejected. Row 3 gets a dashed match but no date. Row 5 has a valid date, but it is a log backup, so the next step will leave it out.

SELECT Id, FileName,
       REGEXP_SUBSTR(FileName, '[0-9]{4}-[0-9]{2}-[0-9]{2}') AS Dashed,
       REGEXP_SUBSTR(FileName, '[0-9]{8}') AS Compact,
       COALESCE(TRY_CONVERT(date, REGEXP_SUBSTR(FileName, '[0-9]{4}-[0-9]{2}-[0-9]{2}'), 23),
                TRY_CONVERT(date, REGEXP_SUBSTR(FileName, '[0-9]{8}'), 112)) AS FileDate
FROM #Files
ORDER BY Id;

Pick the newest backup and break ties

Now rank. Keep only rows with a valid date and a .bak ending. That removes the impossible date and the log backup, even though the log file has the latest date. ROW_NUMBER then numbers the files per database, newest first.

Rows 2 and 4 share the same date, March 16. A tie needs a rule, or the answer can change between runs. I sort by FileName next, then by Id, so the same input always gives the same file.

WITH Parsed AS
(
    SELECT *,
           COALESCE(TRY_CONVERT(date, REGEXP_SUBSTR(FileName, '[0-9]{4}-[0-9]{2}-[0-9]{2}'), 23),
                    TRY_CONVERT(date, REGEXP_SUBSTR(FileName, '[0-9]{8}'), 112)) AS FileDate
    FROM #Files
), Ranked AS
(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY DatabaseName
                              ORDER BY FileDate DESC, FileName, Id) AS rn
    FROM Parsed
    WHERE FileDate IS NOT NULL AND FileName LIKE N'%.bak'
)
SELECT DatabaseName, FileName, FileDate
FROM Ranked
WHERE rn = 1
ORDER BY DatabaseName;

The winner is Sales_20260316.bak, dated March 16. The compact and dashed names both parsed, the bad date was dropped, and the tie went to the shorter name.

A name is a candidate, not proof

This query proves one thing: which file name looks newest. It says nothing about whether the backup finished, is readable, or belongs to the right chain. Before you restore, read the backup header and run a verify on the file. Compare what the file claims with what the name claims.

Also write down your naming rule, including the time zone the date uses. If someone changes the pattern, you want a visible reject, not a silent wrong pick. This demo creates only a temp table, and the last block drops it.

DROP TABLE IF EXISTS #Files;

Parse the names, verify the file, and only then call it your newest backup.

A dated file name is not backup proof, it is a candidate to verify.

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