October 29, 2008 at 7:17 am
I found this procedure in the articles section of this site. Works GREAT but I only get one days worth of History. Can anyone tell me how i can change this to give more than ONE DAYS worth of backup history??????
SET nocount ON
-- drop temp proc if exists
IF object_id('tempdb..#prFileExists') IS NOT NULL
DROP PROCEDURE #prFileExists
GO
-- START create temp proc to check backup file existence
CREATE PROCEDURE dbo.#prFileExists
@path varchar(300) ,
@p2 int OUTPUT
AS BEGIN
/*
DECLARE @EXISTS bit
EXECUTE dbo.#prFileExists 'c:\boot.ini', @EXISTS OUTPUT
SELECT @EXISTS AS [EXISTS]
*/
DECLARE @RES varchar(500)
DECLARE @EXEC varchar(1000)
IF object_id('tempdb..#cmdshell') IS NOT NULL
DROP TABLE #cmdshell
CREATE TABLE #cmdshell (
res varchar (100)
)
SET @EXEC = 'IF exist "' + @path + '" (echo Found) ELSE (echo NOT Found)'
INSERT INTO #cmdshell (res)
EXEC master..xp_cmdshell @EXEC
DELETE #cmdshell WHERE res IS NULL
SET @RES = (SELECT res FROM #cmdshell)
IF @RES = 'Found'
SET @p2 = 1
ELSE
SET @p2 = 0
END
GO
-- END create temp proc to check backup file existence
--Declare variables and temp table for existing backup data
DECLARE @i int
DECLARE @EXISTS bit
DECLARE @location varchar(260)
SET @i = 0
DECLARE @Tmp TABLE (
[ID][int] identity(1,1) NOT NULL,
[FileExists]bit DEFAULT 0,
[DBName] [varchar] (30) NULL ,
[UserName] [varchar] (30) NULL ,
[BackupSize] [varchar] (13) NULL ,
[Duration] [varchar] (10) NULL ,
[BackupAge][int] NULL ,
[FinishDate] [varchar] (20) NULL ,
[Location][varchar] (260) NULL ,
[device_type] [tinyint] NULL
)
-- return data about databases with no backup history
PRINT ' ================================================================================================================================'
PRINT ' ' + @@servername + ' - Database Backup History (SQL Backups only)'
PRINT ' '
if exists(
SELECT
DB.Name
FROM
master..sysdatabases DB
left join
(
select database_name
from msdb..BACKUPSET BS
join msdb..backupmediaset MS
on
BS.media_set_id = MS.media_set_id
join msdb..backupmediafamily MF
on
BS.media_set_id = MF.media_set_id
WHERE
type = 'D'
and mf.device_type <> 7
group by database_name
) BS
on BS.database_name = DB.name
where
BS.Database_name is null
and
not DB.Name = 'TempDB'
)
SELECT
DB.Name as [Databases With No Backup History]
FROM
master..sysdatabases DB
left join
(
select database_name
from msdb..BACKUPSET BS
join msdb..backupmediaset MS
on
BS.media_set_id = MS.media_set_id
join msdb..backupmediafamily MF
on
BS.media_set_id = MF.media_set_id
WHERE
type = 'D'
and mf.device_type <> 7
group by database_name
) BS
on BS.database_name = DB.name
where
BS.Database_name is null
and
not DB.Name = 'TempDB'
else
SELECT
'- None -' as [Databases With No Backup History]
-- get existing backup history data
-- (into table variable for later modification)
PRINT ' Databases With Backup History'
PRINT ' -------------------------------- '
INSERT @Tmp(
[DBName]
, [UserName]
, [BackupSize]
, [Duration]
, [BackupAge]
, [FinishDate]
, [Location]
, [device_type]
)
SELECT
cast(database_name AS varchar(30)) AS [DBName],
cast(user_name AS varchar(30)) AS [UserName],
str(cast(backup_size AS decimal(20,2)) / 1048576 ,10,2) + ' MB' AS [BackupSize],
cast(datediff(n,backup_start_date,backup_finish_date) AS varchar(5)) + ' min.' AS [Duration],
cast(datediff(dd,backup_finish_date,Getdate()) AS varchar(10)) AS [BackupAge],
convert(varchar(20),backup_finish_date) AS [FinishDate],
physical_device_name AS [Location],
mf.device_type
FROM
master..sysdatabases DB
JOIN
msdb..BACKUPSET BS
ON DB.name = BS.database_name
JOIN msdb..backupmediaset MS
ON
BS.media_set_id = MS.media_set_id
JOIN msdb..backupmediafamily MF
ON
BS.media_set_id = MF.media_set_id
JOIN
(
SELECT
max(backup_set_id) AS backup_set_id
FROM
msdb..BACKUPSET BS
JOIN msdb..backupmediaset MS
ON
BS.media_set_id = MS.media_set_id
JOIN msdb..backupmediafamily MF
ON
BS.media_set_id = MF.media_set_id
WHERE
type = 'D'
AND mf.device_type <> 7
GROUP BY database_name
) MaxBackup
ON
BS.backup_set_id = MaxBackup.backup_set_id
WHERE
type = 'D'
-- loop through the results and update the FileExists field
-- (calling temp proc for each row)
SELECT @i = min(ID) FROM @Tmp WHERE ID > @i
WHILE @i IS NOT NULL BEGIN
IF @i IS NOT NULL BEGIN
--PRINT cast(@i AS varchar(20))
SET @location = (SELECT location FROM @Tmp WHERE ID = @i)
EXECUTE #prFileExists @location, @EXISTS OUTPUT
UPDATE @Tmp SET FileExists = @EXISTS WHERE ID = @i
END
SELECT @i = min(ID) FROM @Tmp WHERE ID > @i
END
-- return the results
SELECT
[DBName]
, [UserName]
, [BackupSize]
, [Duration]
, [BackupAge]AS [BackupAge (Days)]
, [FinishDate]
, [FileExists]
, [Location]
FROM
ORDER BY
[BackupAge] DESC
-- drop the temp proc
IF object_id('tempdb..#prFileExists') IS NOT NULL
DROP PROCEDURE #prFileExists
GO
October 29, 2008 at 8:31 am
This query which is part of the procedure you are referencing will return all the backups. So, find this portion of the code and look at the query. Start looking at the where clauses there and see what is filtering the backups that are recorded based on the output of this query.
Hope this helps.
SELECT
cast(database_name AS varchar(30)) AS [DBName],
cast(user_name AS varchar(30)) AS [UserName],
str(cast(backup_size AS decimal(20,2)) / 1048576 ,10,2) + ' MB' AS [BackupSize],
cast(datediff(n,backup_start_date,backup_finish_date) AS varchar(5)) + ' min.' AS [Duration],
cast(datediff(dd,backup_finish_date,Getdate()) AS varchar(10)) AS [BackupAge],
convert(varchar(20),backup_finish_date) AS [FinishDate],
physical_device_name AS [Location],
mf.device_type
FROM
master..sysdatabases DB
JOIN
msdb..BACKUPSET BS
ON DB.name = BS.database_name
JOIN msdb..backupmediaset MS
ON
BS.media_set_id = MS.media_set_id
JOIN msdb..backupmediafamily MF
ON
BS.media_set_id = MF.media_set_id
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 29, 2008 at 11:02 am
As David said change the Where clause.
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) -This will return backups older than 1 week or 7 days.
Just add the above condition in Where clause and change the value specified here(7) as per your requirement.
HTH,
MJ
October 29, 2008 at 11:11 am
Thank you!!!
October 29, 2008 at 2:46 pm
I run this. You can change the WHERE clause to limit the # of days or specify a specific database. It's useful for double checking that everything's getting backed up when it's supposed to
[font="Courier New"]SELECT sysdb.name, bkup.description, bkup.backup_finish_date,
case
when type='D' then '** FULL **'
when type='I' then 'DIFFERENTIAL'
when type='L' then 'LOG'
end as Backup_Type,
(STR(ABS(DATEDIFF(day, GetDate(),(backup_finish_date))))) as 'Days_Ago',
ceiling(bkup.backup_size /1048576) as 'Size Meg' ,
cast((bkup.backup_size /1073741824) as decimal (9,2)) as 'Gig',
server_name, sysdb.crdate
,datediff(minute, bkup.backup_start_date, bkup.backup_finish_date) as 'Mins'
,cast(cast(datediff(minute, bkup.backup_start_date, bkup.backup_finish_date)
as decimal (8,3))/60 as decimal (8,1)) as 'Hours', first_lsn, last_lsn, checkpoint_lsn
FROM master.dbo.sysdatabases sysdb LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
where backup_finish_date > DATEADD(DAY, -60, (getdate())) -- Last 60 days
--AND sysdb.name = 'MY_DB_NAME'
ORDER BY sysdb.name, bkup.backup_finish_date desc[/font]
And this shows the most recent FULL, DIFF, T-LOG or "Never" backup:
[font="Courier New"]SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(char(19), MAX(backup_finish_date), 100), 'NEVER') as LastBackupDate,
case
when type='D' then '** FULL **'
when type='I' then 'DIFFERENTIAL'
when type='L' then 'LOG'
end as Backup_Type,
case
when status > 16 then 'Check DB Status' -- Alert that DB might be ReadOnly, Offline etc...
else ' '
end as 'DB Status'
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name --AND A.type = 'D'
GROUP BY B.name , a.type, status
ORDER BY B.name , LastBackupDate desc,a.type, status[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply