how to get filename of latest file in a directory with t/sql?

Question

Post reply

how to get filename of latest file in a directory with t/sql?

sqlguy-736318

Hall of Fame

Points: 3859
More actions
November 3, 2011 at 10:33 pm

#262321

I'm in the process of creating a database restore script for my staging server.
Whenever I restore a database on my staging server I always restore a bak file with the latest timestamp from a specific filesystem directory. Therefore, I'd like to automate this process in my restore script.
Can you show me the T/SQL I should use to get the filename of the bak file with the latest timestamp from a specific filesystem directory?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sql_dba123 SSC Eights! Points: 853 More actions · Answer 1

Check these links:

http://www.sqlservercentral.com/Forums/Topic595224-357-1.aspx

http://www.sqlservercentral.com/scripts/Backup+%2F+Restore/31995/

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

sqlguy-736318 (11/3/2011)
I'm in the process of creating a database restore script for my staging server.
Whenever I restore a database on my staging server I always restore a bak file with the latest timestamp from a specific filesystem directory. Therefore, I'd like to automate this process in my restore script.
Can you show me the T/SQL I should use to get the filename of the bak file with the latest timestamp from a specific filesystem directory?

What is the format of the filenames that you have? I ask because if they look like the following (has an ISO-style date and time embedded in the name), there is a VERY simple way to do this without using xp_CmdShell, SQLCLR, VBScripts, Powershell, or any other non-built-in method...

dbname_backup_200810292330.bak

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlguy-736318 Hall of Fame Points: 3859 More actions · Answer 3

Jeff Moden (11/4/2011)
sqlguy-736318 (11/3/2011)
I'm in the process of creating a database restore script for my staging server.
Whenever I restore a database on my staging server I always restore a bak file with the latest timestamp from a specific filesystem directory. Therefore, I'd like to automate this process in my restore script.
Can you show me the T/SQL I should use to get the filename of the bak file with the latest timestamp from a specific filesystem directory?
What is the format of the filenames that you have? I ask because if they look like the following (has an ISO-style date and time embedded in the name), there is a VERY simple way to do this without using xp_CmdShell, SQLCLR, VBScripts, Powershell, or any other non-built-in method...
dbname_backup_200810292330.bak

Hi Jeff - The format of the file is MyDB_20111105_1030.bak.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

Oh, shoot. My apologies. I lost track of this thread. Here's the solution given the file name pattern you posted. I hope I'm not too terribly late with it.

DROP TABLE #File

GO

--===== Create a holding table for the file names

CREATE TABLE #File

(

FileName SYSNAME,

Depth TINYINT,

IsFile TINYINT

)

;

--===== Capture the names in the desired directory

-- (Change "C:\Temp" to the directory of your choice)

INSERT INTO #File

(FileName, Depth, IsFile)

EXEC xp_DirTree 'C:\Temp\',1,1

;

--===== Find the latest file using the "constant" characters

-- in the file name and the ISO style date.

SELECT TOP 1

FileName

FROM #File

WHERE IsFile = 1

AND FileName LIKE 'MyDB__20[0-9][0-9][0-1][0-9][0-3][0-9]__[0-2][0-9][0-5][0-9].bak' ESCAPE '_'

ORDER BY FileName DESC

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)