Technical Article

Get backup folder sizes from shared Location

,

Got a message from the Operation team as below

Hi Harsha, I'm seeing a larger than normal rate of change for a week day on the backups for the SQL2000 servers. Did anything run differently yesterday compared to Monday?

I have 22 sql servers and all the Weekend and Monthly backups goes to the location questioned.

To get the whole 22 servers backup and each folder size would had taken a long time. So below is the script what I created to get the total folder level sizes.

To be noted.

  1. Please provide the folder path to the variable @FolderPath
  2. Please provide the server names in the insert list.
  3. The Folder date is in integer format e.g. 20211202

 

--Dir results to store the DIR results of the folder list
if object_id('DIR_Results','U') is not null
drop table DIR_Results
go
create table DIR_Results
(
id int identity(1,1),
Output_result varchar(2000)
)
go

--DIR results to be stored folder level
if OBJECT_ID('DIR_FOlder_list_Results','U') is not null
drop table DIR_FOlder_list_Results
go
create table DIR_FOlder_list_Results
(
id int identity(1,1),
Output_result varchar(2000)
)
go

--DIR results to store Folder list in the path for the server.
if OBJECT_ID('DIR_Folder_List','U') is not null
drop table DIR_Folder_List
go
create table DIR_Folder_List
(
FolderNo int identity(1,1),
Folder_Dateint
)
go

--To store the folder size for each server. 
if OBJECT_ID('Folder_Size','U') is not null
drop table Folder_Size
go

create table Folder_Size
(
Idint identity(1,1),
ServerName varchar(200),
FolderPathvarchar(1000),
FolderNamevarchar(200),
FolderSizevarchar(200)
)
go

--Table to store servernames. 
if OBJECT_ID('Temp_ServerList','U') is not null
drop table Temp_ServerList
go
create table Temp_ServerList
(
Id int identity(1,1),
Servername varchar(200)
)


--Variables
declare @servername varchar(200),
@cmd_executevarchar(2000),
@Server_Count int,
@FolderNamevarchar(200),
@FolderPathvarchar(1000),
@folderCountint
,@folder_comandvarchar(1000)
,@Folder_Size_Srting varchar(200)
,@Folder_Size bigint

select @FolderPath ='sharepathsubfolder'

insert into Temp_ServerList
(Servername)
values('SRV1'),
('SRV2'),
('SRV3'),
('SRV4')


select@Server_Count = COUNT(1)
fromTemp_ServerList


while (@Server_Count>=1)
begin


select @servername = servername 
fromTemp_ServerList
whereID = @Server_Count


select @cmd_execute = 'dir '+@FolderPath +@servername+''

insert into DIR_Results (Output_result)
exec xp_cmdshell @cmd_execute


insert into DIR_Folder_List
(Folder_Date)
selectright(Output_result,8) 
fromDIR_Results
whereOutput_result is not null
and Output_result not like '%Volume %'
and Output_result not like '%dire%'
and  Output_result not like '%file(s)%'
and  Output_result not like '%dir(s)%'
and  Output_result not like '%.%'
and  Output_result not like'%..%'

select  @folderCount = COUNT(1) 
fromDIR_Folder_List


while(@folderCount>=1)
begin



select @FolderName = Folder_Date
fromDIR_Folder_List

select @folder_comand = 'dir '+ @FolderPath +@servername +''+@FolderName

insert into DIR_Folder_list_Results(Output_result)
exec xp_cmdshell @folder_comand


select@Folder_Size_Srting = Output_result
fromDIR_FOlder_list_Results
whereOutput_result like '%file(s)%'


select @Folder_Size =ltrim(rtrim
(REPLACE(
REPLACE(
substring(@Folder_Size_Srting,
CHARINDEX('(s)',@Folder_Size_Srting)+3 ,
CHARINDEX('bytes',@Folder_Size_Srting)),
'bytes',''),
',',''))
)


insert into Folder_Size
(
ServerName
,FolderPath
,FolderName
,FolderSize
)
select @Servername,
@FolderPath+''+@servername,
@FolderName,
@Folder_Size/1024/1024/1024

select @folderCount = @folderCount-1
end

truncate table DIR_Folder_List;
truncate table DIR_FOlder_list_Results;
truncate table DIR_Results

select @Server_Count = @Server_Count -1
end;


select * from Folder_Size

Rate

Share

Share

Rate