TSQL - 如何从文件中读取日期时间与xp_dirtree
问题描述:
我与使用xp_dirtree从多个BAK-文件的目录拿起一个文件名,然后TSQL脚本工作恢复到数据库使用选定的文件名和目录。TSQL - 如何从文件中读取日期时间与xp_dirtree
但是,我想能够从所述文件夹中选择最近的文件。目前,该脚本仅在按文件名排序后才选择顶部。
的文件夹可能是这个样子:
------------
File1.BAK
File2.BAK
File3.BAK
File4.BAK
------------
我的剧本目前看起来是这样的:
DECLARE @dir varchar(60)
SET @dir = 'C:\TestFolder\'
CREATE TABLE #DirectoryTree (
id int IDENTITY(1,1)
,subdirectory nvarchar(512)
,depth int
,isfile bit);
INSERT #DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree @dir,1,1;
DECLARE @file varchar(60) =
(SELECT TOP 1 subdirectory
FROM #DirectoryTree
WHERE isfile = 1 AND RIGHT(subdirectory,4) = '.BAK'
ORDER BY subdirectory DESC)
SET @dir = @[email protected]
PRINT '--Selected file: "'[email protected]+'"'
GO
DROP TABLE #DirectoryTree
--restores from file
restore database TESTDB
from disk = @dir
with replace, file = 1
答
Dirtree不返回的信息。 Xp_cmdshell的确如此,它必须在您的dba可能或不可以允许的实例上启用。如果这里是解决方案:
create table #files (name varchar(500))
insert into #files
EXEC xp_cmdshell 'dir *.exe';
-- find name after list space in string....
select name, REVERSE(SUBSTRING(REVERSE(name),0,CHARINDEX(' ',REVERSE(name))))
from #files
-- dates start with numeric --check assumption carefully...
where isnumeric(left(name,1))=1
--order by date desc --
order by CAST(left(name,17) as datetime) desc