Frequently you'll want to know how fast your database-backup has been growing. Perhaps you have implement a SAN Backup-Device for Backup-to-disk, or you only want know how fast your database has been growing.
This TSQL statement will query the [msdb]..[backupset], to roughly outline the growth of your database-backup, for each user-database of the local server.
DECLARE @command VARCHAR(1000)
CREATE TABLE #db_backup_grow (DBName sysname, BackupDate sysname, SizeInGB sysname)
SELECT @command =
'USE [?] SELECT ''?'' AS DBName
,BackupDate = CONVERT(VARCHAR(10),[backup_start_date], 111)
,SizeInGB = ([backup_size]/1073741824)
FROM [msdb]..[backupset]
WHERE [database_name] = ''?''
AND [type]=''D''
ORDER BY [backup_start_date] DESC'
INSERT INTO #db_backup_grow
EXEC sp_MSforeachdb @command
SELECT *
FROM #db_backup_grow
WHERE DBName not in ('master', 'model', 'msdb', 'tempdb')
ORDER BY DBName, BackupDate, SizeInGB
DROP TABLE #db_backup_grow
CU
tosc
This entry was posted by tosc and is filed under SQL Server. Tags: backup, sp_msforeachdb, sql server 2005, t-sql