This procedure gets the date a file was updated, as reported by the file system.
exec sp_configure 'show advanced options', 1;
reconfigure;
exec sp_configure 'xp_cmdshell',1;
reconfigure;
go
use master
go
create procedure [dbo].[get_file_date](
@file_name varchar(max)
,@file_date datetime output
) AS
BEGIN
set dateformat dmy
declare @dir table(id int identity primary key, dl varchar(2555))
declare @cmd_name varchar(8000),@fdate datetime,@fsize bigint, @fn varchar(255)
set @fn=right(@file_name,charindex('\',reverse(@file_name))-1)
set @cmd_name='dir /-C "'+@file_name+'"'
insert @dir
exec master..xp_cmdshell @cmd_name
select @file_date=convert(datetime,ltrim(left(dl,charindex(' ',dl))),103)
from @dir where dl like '%'+@fn+'%'
end
go
usage -
declare @file_date_op datetime
exec master.dbo.get_file_date
@file_name = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\MSDBData.mdf'
,@file_date = @file_date_op OUTPUT
SELECT @file_date_op
1 comment:
Just a note from your other post that we saw 101 being important if you are dealing with US instead of 103 for the UK.
http://sqlsolace.blogspot.com/2011/09/are-my-databases-being-used.html?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+SqlSolace+%28sql+solace%29
Post a Comment