CODEWITHSUNDEEP
sqlsql-server
BDEZ
BDEZ

Reputation: 27

Last modification Date file system by creating a script

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

Upvotes: 0

Views: 696

Answers (2)

BDEZ
BDEZ

Reputation: 27

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"

}

Upvotes: 0

pwilcox
pwilcox

Reputation: 5763

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.

Upvotes: 1

Related Questions