How to find size of backup files in a folder?

I was asked to findout total size of all the backup files in the folder
As I didnt have access to view the folder of the server, I used this.
I hope it may be helpful

create table #t(files varchar(1000))

Insert into #t
EXEC master..xp_cmdshell 'dir e:\backup'

substring(size_files,charindex(' ',size_files)+1 ,len(size_files))as backup_file,
cast(substring(size_files,1,charindex(' ',size_files)-1) as money)/1024/1024 as file_size_MB
        select files,ltrim(substring(files,patindex('%[ AP ]M%',files)+2,len(files))) as size_files 
        from #t where files like '%bak'
) as t

drop table #t

Published Thursday, May 7, 2009 11:04 AM by Madhivanan


No Comments