SQL Server Last modification Date file system by creating a script

mfuanj7w 于 2024-01-05 发布在其他

关注(0)|答案(2)|浏览(152)

I need to create an script or query to give me the last modification date of a file system. I have the below query and it works perfectly for an overwitted backup file. but I need to run it for *.bak that shows me the latest backup file modification date during many backup files:

if exists(select 1 from tempdb..sysobjects where name='##tmp')
drop table ##tmp
create table ##tmp(mdate varchar(8000))
insert ##tmp
exec master.dbo.xp_cmdshell 'dir g:\SQL_Backup\filename.bak' -- (I need the --last backup file name which the name keep changing every week)
set rowcount 5
delete from ##tmp
set rowcount 0
select top(1) substring(mdate,1,20) as 'Last modified date' from ##tmp

sql-server

来源：https://stackoverflow.com/questions/58204966/last-modification-date-file-system-by-creating-a-script

2条答案

按热度按时间

tnkciper1#

I've never worked with these views, so this really need a reality check before you really integrate it, but this may help:

select      
top 1       backupSetName = bs.name,
            backupFilePath = bf.physical_name, 
            backupFileLogicalName = bf.logical_name, 
            bs.backup_start_date,
            bs.backup_finish_date
from        msdb.dbo.backupfile bf
join        msdb.dbo.backupSet bs on bf.backup_set_id = bs.backup_set_id
where       right(bf.physical_name,4) = '.mdf'
order by    backup_finish_date desc;

"Backup Set" records the actual dates. Since it is one-to-many with "Backup Files", it means that the '.mdf' and '.ldf' file dates are recorded together. I just excluded the latter in the above output.

赞(0）回复(0）举报 2024-01-05

t9eec4r02#

I can run the PowerShell script and store in in sql server job. It ran successfully.

$source = "\\00.0.00.00\your file location"
$filetype = "dif" (or "bak")
Get-ChildItem "$source\*" -Include "*.$filetype" | sort LastWriteTime | select -last 1
$datetime=[datetime]::Today
if($datetime.DayOfWeek -match 'Monday'){
    Write-Host "YES"
}elseif($datetime.DayOfWeek -match 'Tuesday|Thursday|Saturday'){
    Write-Host "NO"
}else{
    
    Write-Host "YES"
}

展开查看全部

赞(0）回复(0）举报 2024-01-05

我来回答

SQL Server Last modification Date file system by creating a script

2条答案

相关问题

热门标签

最新问答