How to find size of SQL backup files in a folder?
I was asked to findout total size of all the backup files in the folderAs 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 #tEXEC master..xp_cmdshell 'dir e:\backup'
select
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 from( select files,ltrim(substring(files,patindex('%[ AP ]M%',files)+2,len(files))) as size_files from #t where files like '%bak') as t
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 from( 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