January 8, 2008 at 9:39 am
Hi all!
i need a script to check the result (success or failed) of the backups dbs in sql server 2005.
I would like to schedule it, and to put the result in a table or a file.
thanks for your help!
January 8, 2008 at 11:16 am
This is a script I pulled from this site some time ago. It displays databases with no backup history and also displays the history of the databases that do have history. It will tell you the end data of the backup and where or not the file exists.
/*
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 ' ================================================================================================================================'
January 8, 2008 at 11:20 am
You can right click on the Job that runs the backup and go to properties. On the left hand side choose Notifications. There you can set SQL server to email you when the job fails/succeeds or finishes (of which the email will tell you result of the job). By default SQL Server already writes to the Application event log when the job fails. If you want it to email you you need to set up operators and configure Database mail.
=============================================================
/* Backups are worthless, Restores are priceless */
Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management
January 8, 2008 at 12:21 pm
"The only good backup is one that has been sucessfully restored" If you want to check backups restore them, absolutely nothing else guarantees a backup.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 8, 2008 at 12:46 pm
Collin has a very good point here. The only sure fire way to determine if a backup is "good" is to restore; however, you can also add some additional processing to the script to aid in checking the validity of a backup. You can tell whether or not a backup is valid by using the RESTORE VERIFYONLY option. DISCLAIMER: Just because SQL says the backup is valid it does not mean that you are 100% safe; it simple means the basic check criteria were met.
e.g.
RESTORE VERIFYONLY
FROM DISK = N'C:\test.bak'
More info on RESTORE VERIFYONLY
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/cba3b6a0-b48e-4c94-812b-5b3cbb408bd6.htm
January 9, 2008 at 1:58 am
i will do the tests!
thanks to all!!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply