January 26, 2011 at 10:38 pm
Hi All,
I have 2 queries below which will give Backups every Information only when the backup file is there in the Disc/Drive.But in our case we need to get the details of backups file sizes for past 90 days to now.Is it possible to get that info through MSDB? If yes, please help me on this.
Query to Get FileGrowthInfo from Backup History
/***********************************************************
Check growth of .LDF and .MDF from backuphistory.
Lines returned depends on the frequency of full backups
Parameters: database name
fromdate (date from which info is requiered in
smalldatetime)
Results best viewed in grid
***********************************************************/
--- Change these vars for your database
declare @dbname varchar(128)
declare @fromdate smalldatetime
select @dbname = 'YourDbName'
select @fromdate = getdate()-30 ---filegrowth last 30 days
create table #sizeinfo
(
filedate datetime null,
dbname nvarchar(128) null,
Dsize numeric (20,0) null,
Lsize numeric (20,0) null,
backup_set_id int null,
backup_size numeric (20,0) null
)
--- tmp pivot table to get mdf en ldf info in one line
insert #sizeinfo
select
filedate=bs.backup_finish_date,
dbname=bs.database_name,
SUM(CASE file_type WHEN 'D' THEN file_size ELSE 0 END) as Dsize,
SUM(CASE file_type WHEN 'L' THEN file_size ELSE 0 END) as Lsize,
bs.backup_set_id,
bs.backup_size
from msdb..backupset bs, msdb..backupfile bf
where bf.backup_set_id = bs.backup_set_id
and rtrim(bs.database_name) = rtrim(@dbname)
and bs.type = 'D'
and bs.backup_finish_date >= @fromdate
group by bs.backup_finish_date, bs.backup_set_id, bs.backup_size, bs.database_name
order by bs.backup_finish_date, bs.backup_set_id, bs.backup_size, bs.database_name
select
Date=filedate,
Dbname=dbname,
MDFSizeInMB=(Dsize/1024)/1024,
LDFSizeInMB=(Lsize/1024)/1024,
TotalFIleSizeInMB=((Dsize+Lsize)/1024)/1024,
BackupSizeInMB=(backup_size/1024)/1024
from #sizeinfo
order by filedate
drop table #sizeinfo
Query to get Full Details of Backup's from MSDB
SELECT database_name, backup_start_date, type, *
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'D'
ORDER BY backup_set_id DESC
GO
Thanks in advance,
vamshi.
January 27, 2011 at 1:45 am
USE tempdb
GO
-- Get Backup History for required database
SELECT
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME()
--and s.backup_start_date > 'from date' and s.backup_start_date <= 'till date' -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO
will give you information about the current database where you will run the query and also depends on the history information saved in msdb.
----------
Ashish
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply