March 12, 2007 at 10:10 am
How can I find out the sizes of past db backup files and transaction log backup files?
March 12, 2007 at 2:24 pm
Well the obvious answer is to look on your backup volumne to see the size of backups!!
However, I assume you mean going beyond the number of backups on disk or tape? There is no record of backup size within SQL Server that I know of. As far as I know, SQL Server only recoreds the fact that the scheduled job has run/failed etc in the scheduled job logs and SQL error logs.
Generally, I log the backup size (full and logs) in excel once a month as well as the database growth so I can plan for any possible disk expansion. Another process I should really get round to automating!
March 12, 2007 at 3:32 pm
This will log the result of RESTORE HEADERONLY to a table. The backup size is one of the fileds. You can use it in a loop for all files in the backup directory. You can get the backup file list by using xp_cmdshell with dir command.
declare
@sql nvarchar(500)
select
@sql = 'RESTORE HEADERONLY from disk = ' + '''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\master_backup_200703121215.bak'''
select
@sql
Create
Table #TempTable(
BackupName nvarchar(128),
BackupDescription nvarchar(255) ,
BackupType smallint ,
ExpirationDate datetime ,
Compressed tinyint ,
Position smallint ,
DeviceType tinyint ,
UserName nvarchar(128) ,
ServerName nvarchar(128) ,
DatabaseName nvarchar(128) ,
DatabaseVersion int ,
DatabaseCreationDate datetime ,
BackupSize numeric(20,0) ,
FirstLSN numeric(25,0) ,
LastLSN numeric(25,0) ,
CheckpointLSN numeric(25,0) ,
DatabaseBackupLSN numeric(25,0) ,
BackupStartDate datetime ,
BackupFinishDate datetime ,
SortOrder smallint ,
CodePage smallint ,
UnicodeLocaleId int ,
UnicodeComparisonStyle int ,
CompatibilityLevel tinyint ,
SoftwareVendorId int ,
SoftwareVersionMajor int ,
SoftwareVersionMinor int ,
SoftwareVersionBuild int ,
MachineName nvarchar(128) ,
Flags int ,
BindingID uniqueidentifier ,
RecoveryForkID uniqueidentifier ,
Collation nvarchar(128) ,
FamilyGUID uniqueidentifier ,
HasBulkLoggedData bit ,
IsSnapshot bit ,
IsReadOnly bit ,
IsSingleUser bit ,
HasBackupChecksums bit ,
IsDamaged bit ,
BeginsLogChain bit ,
HasIncompleteMetaData bit ,
IsForceOffline bit ,
IsCopyOnly bit ,
FirstRecoveryForkID uniqueidentifier ,
ForkPointLSN numeric(25,0) NULL ,
RecoveryModel nvarchar(60) ,
DifferentialBaseLSN numeric(25,0) NULL ,
DifferentialBaseGUID uniqueidentifier ,
BackupTypeDescription nvarchar(60) ,
BackupSetGUID uniqueidentifier NULL
)
Insert
into #TempTable
Execute
sp_executesql @sql
select
* from #TempTable
drop
table #TempTable
Regards,Yelena Varsha
March 13, 2007 at 5:53 am
check out the backupfile table in msdb
March 13, 2007 at 7:13 am
check the filesystem or tape logs as well.
March 13, 2007 at 12:55 pm
You can get the size of backups from msdb.dbo.backupset. If you use split backups you have more than one file per backup, so you don't want the individual file sizes. I use this script to get the full backup size by date. I offset the dates by 12 hours so each 'date' runs from noon on the date shown until noon the next day, so overnight jobs are considered the same date. The past history available depends on your history cleanup settings.
SELECT database_name,
REPLACE(CONVERT(VARCHAR, CAST(backup_size AS MONEY), 1), '.00', '') AS backup_size,
CONVERT(CHAR(10), DATEADD(hh,12,backup_start_date), 23)
FROM backupset
WHERE TYPE = 'D'
ORDER BY database_name, backup_start_date
If you have transaction log backups you probably have many per day, so you probably want the total of all files or the maximum size rather than the individual files. This query shows the total size of all backup types grouped by database and date.
SELECT database_name, CAST(SUM(backup_size)/1024.0/1024.0/1024.0 AS DECIMAL(10,3)) AS backup_size_gb, backup_start_date
FROM (SELECT database_name, backup_size, CONVERT(CHAR(10), DATEADD(hh, 12, backup_start_date), 23) AS backup_start_date
FROM backupset) AS x
GROUP BY database_name, backup_start_date
ORDER BY database_name, backup_start_date
March 13, 2007 at 2:06 pm
Thanks for all the replies. Very helpful.
March 14, 2007 at 8:45 am
Nice. Had not come across that table in msdb...
March 14, 2007 at 10:33 am
If you put the queries I posted above, especially the second one, into an Excel spreadsheet and turn it into a pivot table, it looks very impressive. Just the thing you need when you have to go argue for more or faster backup hardware. Add charts if your management is hard to convince.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply