如何从文件夹获取数据库的最新备份？

Question

我想获取文件夹中最新备份的完整路径。

我怎么会在SQL Server中得到这个？

Answer 1

您可以使用master.sys.xp_dirtree

此代码将让你最上面的文件目录中，授予您的备份对他们的日期。 最高日期应该是目录文件结构中的最高文件。

您的备份文件路径最终将在@LatestBackupFilepath

DECLARE @BackupDirectory NVARCHAR(255) 
DECLARE @LatestBackupFilepath NVARCHAR(255); 

SET @BackupDirectory = 'C:\YourBackupFolder' 

IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL 
     DROP TABLE #DirectoryTree; 

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 @BackupDirectory, 1, 1; 

;With LatestBackup 
AS 
(
    SELECT 
     TOP 1 subdirectory AS [Filename] 
    FROM 
     #DirectoryTree 
    WHERE 
     isfile = 1 
     AND RIGHT(subdirectory,4) = '.BAK' 
    ORDER BY 
     id 
) 
SELECT 
    @LatestBackupFilepath = CONCAT(@BackupDirectory, '\', [Filename]) 
    FROM LatestBackup 

DROP TABLE #DirectoryTree;