Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Server Backup History Report (updated 2006-04-11)


Server Backup History Report (updated 2006-04-11)

Author
Message
mops33
mops33
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 169
Comments posted to this topic are about the item Server Backup History Report (updated 2006-04-11)



Jpotucek
Jpotucek
Right there with Babe
Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)

Group: General Forum Members
Points: 790 Visits: 1648
This procedure works GREAT - but it only give the most recent backup.. not a full History...

my output attached.

Does anyone know why I am only getting one days worth of history???



Attachments
KOCSQL02_Backup_hist.xlsx (35 views, 10.00 KB)
m.rana.reddy
m.rana.reddy
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 11
hey, were you able to get a complete history of the backups using this procedure as against only one day's backup history?
Nadrek
Nadrek
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1029 Visits: 2673
For anyone looking at this now:
A) This does also work on SQL Server 2005, 2008 and 2008 R2, both 32 and 64 bit.
B) This is, of course, subject to the "overwritten backup devices (files) don't update msdb.dbo.backupmediafamily physical device name (location) bug in SQL Server 2005 and below:
http://connect.microsoft.com/SQLServer/feedback/details/306604/physical-device-name-not-captured-correctly-in-msdb-dbo-backupmediafamily
C) I've rolled in gabriel.defigueiredo's update to use xp_fileexist instead of xp_cmdshell; not only does it work when xp_cmdshell is disabled (verified on a 2008 box), I've tested it as being on the order of 5 times faster, as well (this becomes important when you remove the "only show the latest" join, to show all backups).


-- Originally from http://www.sqlservercentral.com/Forums/Topic440139-146-1.aspx and http://www.sqlservercentral.com/Forums/Topic705510-599-1.aspx
/*
Server Backup History Report
(Full DB Backups only)
1. lists all databases with no backup history
2. lists last backup for other databases
includes Date, User, Size, Duration, Age, Finish Date & Location

Includes system databases
Excludes TempDB

Excludes backup history data where backupmediafamily.device_type = 7
these are typically created by Veritas BackupExec

Tested on SQL Server 2000 (SP3)

Brett Ivery 24/03/2006 created
11/04/2006 added file existence check
Other: 20110222 updated to not use xp_cmdshell, per http://www.sqlservercentral.com/Forums/Topic705510-599-1.aspx

*/
SET nocount ON
USE tempdb
-- 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
IF object_id('tempdb..#FileExists') IS NOT NULL
DROP TABLE #FileExists
CREATE TABLE #FileExists ([File Exists] int, [File is a Directory] int, [Parent Directory Exists] int)
INSERT INTO #FileExists exec master..xp_fileexist @path

SELECT @p2 =[File Exists] from #FileExists
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
@tmp
ORDER BY
[BackupAge] DESC

-- drop the temp proc
IF object_id('tempdb..#prFileExists') IS NOT NULL
DROP PROCEDURE #prFileExists
GO

PRINT ' ================================================================================================================================'




Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search