SQL Script to find last full backup size

• Saggy

  SSCrazy

  Points: 2003

  More actions

  March 14, 2014 at 11:22 pm

  #283480

  Hi Guys,

  I need sql query to find only last full backup size on shared drive or network location.

  for eg: \\10.10.10.10\sql_backup\test.bak

  size: 1GB

  if full backup perform yesterday then i want to retrieve the size of last full backup only.

  SQL Server version: 2005/2008

  thnx......reply

• SQLBuddy123

  One Orange Chip

  Points: 28294

  More actions

  March 18, 2014 at 10:06 am

  #1698108

  Try this .. You can add a filter for Backup Location in the Where Clause ..

  SELECT bs.database_name AS DbName

  , CAST ( bs.backup_size / 1024.0 / 1024 / 1024 AS DECIMAL(10, 2) ) AS BackupSize_GB

  , bs.backup_start_date AS Backup_StartDate

  , bs.backup_finish_date AS Backup_EndDate

  , bs.name AS BackupName

  , bmf.physical_device_name AS DeviceName

  FROM msdb.dbo.backupset bs JOIN msdb.dbo.backupmediafamily bmf

  ON bs.media_set_id = bmf.media_set_id

  WHERE bs.database_name = 'DB_Name' and bs.backup_start_date <= DateAdd(hh, -24, GETDATE()) and bs.type = 'D'

  order by bs.backup_start_date desc

  --

  SQLBuddy