question

VinothKumarAnnadurai-4871 avatar image
0 Votes"
VinothKumarAnnadurai-4871 asked VinothKumarAnnadurai-4871 commented

SQL server capacity plannin

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!

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Declare @stdt varchar(30)
Declare @enddt varchar(30)
set @stdt = convert(varchar, getdate()-7,101)
set @enddt = convert(varchar, getdate(),120)
SELECT
bkSet.Database_Name As DatabaseName,
bkSet.Backup_Finish_Date ,
CAST((bkSet.Backup_Size/1048576) AS NUMERIC(10,2)) AS BackupSizeInMB,
bkset.compressed_backup_size/1048576 compressed_bkp_sizeMB
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 bkSet.Database_Name in ('TDE_DEVOPS_BITBUCKET','TDE_DEVOPS_BITBUCKET2') and
--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'
ORDER BY
bkSet.Backup_Finish_Date DESC

0 Votes 0 ·
AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VinothKumarAnnadurai-4871 avatar image
0 Votes"
VinothKumarAnnadurai-4871 answered

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'

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.