Need to know backup size of my database

deepak03

Hi,

I am using red-gate on my n number of servers, i want to know the compress backup size of my database using some command in SQL Query analyser. I need to use it in few of my SQL jobs to monitor disk space on my server. Can you help me in that?

Thanks,

petey

SQL Backup stores details of its backup and restore processes in a SQL Server Compact database, whose default location is '<system drive>Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Data\<instance name>'.

The tables you might want to use are:

- backuphistory
Will provide you details on the database backed up, and total backup size

- backupfiles
Provides file size for each individual file, useful if you split your backup files across different drives.

You can use the 'sqbdata' extended stored procedure to access data in the SQL Compact database e.g.

EXEC master..sqbdata 'SELECT * FROM backuphistory WHERE dbname = ''AdventureWorks'' AND backup_type = ''D'' '

If you need to link to the standard SQL Server backup history tables, use the backup_set_uuid value in backup history to the msdb..backupset table.

jerryhung

This is what I use for Backup summary
Even better if you run this across the Central Management Servers (CMS) to grab a summary of ALL the servers in CMS

EXEC master..sqbdata '
SELECT  dbname
	   ,backup_start, backup_end
       ,backup_type       
       ,duration AS [seconds]
       ,BH.size/1000 AS [Size_KB]
       ,BH.compressed_size/1000 AS [CompressedSize_KB]
       ,speed,encryption
       ,compression_ratio
       ,compression_level       
       ,BF.name AS filename
       --,logfilename
       --,convert(nvarchar(19),backup_start,100) as Backup_Start
       ,DATEPART(hh,backup_start) AS [hour]
FROM    backuphistory BH
        INNER JOIN backupfiles BF ON BH.id = BF.backup_id
WHERE 1=1
	AND DATEDIFF(d,backup_start, getdate()) = 1 -- less than X days old
	AND backup_type = ''L''
	AND dbname = ''XXXX''
ORDER BY BH.id DESC     
'