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.
- Please provide the folder path to the variable @FolderPath
- Please provide the server names in the insert list.
- 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_Date int
)
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
(
Id int identity(1,1),
ServerName varchar(200),
FolderPath varchar(1000),
FolderName varchar(200),
FolderSize varchar(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_execute varchar(2000),
@Server_Count int,
@FolderName varchar(200),
@FolderPath varchar(1000),
@folderCount int
,@folder_comand varchar(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)
from Temp_ServerList
while (@Server_Count>=1)
begin
select @servername = servername
from Temp_ServerList
where ID = @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)
select right(Output_result,8)
from DIR_Results
where Output_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)
from DIR_Folder_List
while(@folderCount>=1)
begin
select @FolderName = Folder_Date
from DIR_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
from DIR_FOlder_list_Results
where Output_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
