Need to know backup size of my database

• 
  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.

  petey July 18, 2010 22:05

  - 0 +
• 
  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     
  '
  

  jerryhung July 19, 2010 14:10

  - 0 +

Add comment

Please sign in to leave a comment.