SQL server capacity plannin

Question

I am doing capacity planning for one of my SQL servers that I support. I would like to get the script which get the size of backup files for the databases for a month , based on which I will calculate for a year and so on as per our requirement. Thanks in advance!

Answer 1

Hi VinothKumarAnnadurai-4871,
Please check:

Declare @startdate varchar(30)  
Declare @enddate varchar(30)  
SET @startdate = convert(varchar, getdate()-30,101)  
SET @enddate = convert(varchar, getdate(),120)  
SELECT   
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,   
   bkSet.database_name,   
   bkSet.backup_start_date,   
   bkSet.backup_finish_date,   
   bkSet.expiration_date,   
   CASE bkSet.type   
      WHEN 'D' THEN 'Database'   
      WHEN 'L' THEN 'Log'   
      END AS backup_type,   
   bkSet.backup_size,   
    MedFam.logical_device_name,   
    MedFam.physical_device_name,   
   bkSet.name AS backupset_name,   
   bkSet.description   
FROM   
   msdb.dbo.backupmediafamily MedFam  
   INNER JOIN msdb.dbo.backupset bkSet ON  MedFam.media_set_id = bkSet.media_set_id   
WHERE   
   bkSet.backup_start_date BETWEEN @startdate AND @enddate  
ORDER BY   
   bkSet.database_name,   
   bkSet.backup_finish_date  

Best Regards,
Amelia

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Declare @stdt varchar(30)
Declare @enddt varchar(30)
set @stdt = convert(varchar, getdate()-15,101)
set @enddt = convert(varchar, getdate(),120)
SELECT
distinct
left(MedFam.Physical_Device_Name , patindex('%mount%',MedFam.Physical_Device_Name)+6) AS BackupPath
FROM
msdb..BackupMediaFamily MedFam
INNER JOIN
msdb..BackupMediaSet MedSet
ON
MedFam.Media_Set_ID = MedSet.Media_Set_ID
INNER JOIN
msdb..BackupSet bkSet
ON
bkSet.Media_Set_ID = MedSet.Media_Set_ID
WHERE
--keep your database name in condition
-- --put the date between which you want to find details of backup
bkSet.Backup_Finish_Date BETWEEN @stdt AND @enddt
and
MedFam.Physical_Device_Name like '%.bak'