Need to know backup size of my database

deepak03deepak03 Posts: 7
edited July 19, 2010 2:10PM in SQL Backup Previous Versions
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,
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • jerryhungjerryhung Posts: 138
    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 &#91;seconds&#93;
       ,BH.size/1000 AS &#91;Size_KB&#93;
       ,BH.compressed_size/1000 AS &#91;CompressedSize_KB&#93;
       ,speed,encryption
       ,compression_ratio
       ,compression_level       
       ,BF.name AS filename
       --,logfilename
       --,convert&#40;nvarchar&#40;19&#41;,backup_start,100&#41; as Backup_Start
       ,DATEPART&#40;hh,backup_start&#41; AS &#91;hour&#93;
FROM    backuphistory BH
        INNER JOIN backupfiles BF ON BH.id = BF.backup_id
WHERE 1=1
	AND DATEDIFF&#40;d,backup_start, getdate&#40;&#41;&#41; = 1 -- less than X days old
	AND backup_type = ''L''
	AND dbname = ''XXXX''
ORDER BY BH.id DESC     
'
    Jerry Hung
    DBA, MCITP
    · Share on Twitter
Sign In or Register to comment.