March 6, 2008 at 9:04 am
I need a query that gets all of the databases on a server and determines if a backup has been taken. I would normally use the jobs query I currently have, but it wouldn't tell me when the last backup was taken for a new database. I think I should be able to use sp_helpdb or some table from the msdb database. Also, this has to work on SQL Server 2000/2005. If anyone has anything I'd be very grateful. Thanks in advance.
-Kyle
March 6, 2008 at 10:49 am
I found this procedure from the code repository on this site :).
/*
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 Ivery24/03/2006created
11/04/2006added file existence check
*/
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
PRINT ' ================================================================================================================================'
March 6, 2008 at 12:54 pm
Depending on what you're looking to do, if it's as simple as look for the last date of a backup ... this should work. There is much more you can do with this obviously, but it works if that is what you're attempting to do.
SELECT
sd.dbid
,sd.name
,ISNULL(CONVERT(VARCHAR(20),MAX(bu.backup_finish_date),100),'No Backup') AS [LastBackup]
FROM msdb.dbo.backupset bu
FULL OUTER JOIN master.dbo.sysdatabases sd
ON sd.dbid = DB_ID(bu.database_name)
WHERE sd.name <> 'tempdb'
AND bu.type = 'D'
GROUP BY sd.dbid, sd.name
ORDER BY 1
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply