SQL SERVER BACKUP MONITOR - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL SERVER BACKUP MONITOR

Author

Topic

fvinagre
Starting Member

29 Posts

Posted - 2004-10-07 : 05:10:04

Hi DBA's!!!

I'm looking for a solution for this issue:

I have a big instalation of sql servers, many of them use the sql server backup to make security copies.

When I going to check the result of the backup I must connect to each server to see if the backup has run succesfuly or not. This is very heavy work and This task takes me long time.

I don't have a good skills in transact SQL. Maybe you can help me.

My first Idea is create a dabatase as a repository in a server an collect the necesary information of the MSDB of each server and transfer to this repository.

The implementation is easy, include a second step on each backup job that copy the data on the repository server. Then I can check backups making a simple query.

Any more ideas, any tool to monitor sql server backups from serveral servers.

How I can do it?

Thank's In Advance
Fernando

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-07 : 05:21:27

you can mail the result or save it in a table.

if you're backup procedure is scripted you can include in your script to return the error code, if it's 0 then success otherwise failed, then save the data in table with whatever information you want, and create a trigger on that table when something is inserted, send a mail.

fvinagre
Starting Member

29 Posts

Posted - 2004-10-07 : 05:35:34

How i can include the return error code?
How i can get the rows about the date, time, size, ...?

fvinagre
Starting Member

29 Posts

Posted - 2004-10-07 : 05:35:39

How i can include the return error code?
How i can get the rows about the date, time, size, ...?

biged123456
Starting Member

4 Posts

Posted - 2004-10-07 : 14:06:25

I wrote a script to do some of what you are looking for. It will pull from MSDB everything that doesn't have a full in 8 days and TL backup in 2 (you can configure these for your environment). You can run it as a SQL job. I have it set to fail if backups aren't current. Just setup a job operator to email you if it fails. By the way, I am by no means a developer and the use of cursors is frowned upon, but I only run it once a night, so...
---

--This query is written to check for recent full and transaction log SQL dumps.
--If dumps are not found, it will log an error to the Windows Application log with
--the database names and will fail the job.

USE MASTER

--Start Cursor stuff
SET NOCOUNT ON
DECLARE backupcheck_cursor CURSOR FOR

--Query to find all DBs without recent Full backups
(select SD.name from master..sysdatabases SD
where SD.name not in
--excludes all recent fulls
(select distinct BS.database_name
from msdb..backupset BS
where BS.type = 'D' and BS.backup_finish_date > (select DATEADD(dd, -8, GetDate())))
--exclude misc databases
and SD.name <> 'pubs' and SD.name <> 'northwind'
and name <> 'tempdb' and name <> 'model')

--Continue Cursor stuff
Open backupcheck_cursor
DECLARE @@DBfailedname varchar(50)
DECLARE @@MESSAGE varchar(255)
DECLARE @@MyErrorNumber INT
FETCH NEXT FROM backupcheck_cursor
INTO @@DBfailedname

--Start looping over recordset
WHILE @@FETCH_STATUS = 0
BEGIN

--If most recent Full backup is older than 8 days, log error
SELECT @@MESSAGE = 'The database "' + @@DBfailedname +
'" has no full backups within the past 8 days.'
EXEC xp_logevent 60000, @@MESSAGE, warning
FETCH NEXT FROM backupcheck_cursor
INTO @@DBfailedname
--If failures, set the @MyErrorNumber to 1
SET @@MyErrorNumber = 1
END

--Close the Cursor stuff
CLOSE backupcheck_cursor
DEALLOCATE backupcheck_cursor

--
--Transaction log portion
--
--Start Cursor stuff
SET NOCOUNT ON
DECLARE backupcheck_cursor2 CURSOR FOR

--Query to find all DBs without recent TL backups
select SD.name from master..sysdatabases SD
where SD.name not in
--excludes all recent TL backups
(select distinct BS.database_name
from msdb..backupset BS
where BS.type = 'L' and BS.backup_finish_date > (select DATEADD(dd, -2, GetDate())))
--exclude misc databases
and SD.name <> 'pubs' and SD.name <> 'northwind'
and name <> 'tempdb' and name <> 'model'
and name <> 'master' and name <> 'msdb'

--Continue Cursor stuff
Open backupcheck_cursor2
DECLARE @@DBfailedname2 varchar(50)
DECLARE @@MESSAGE2 varchar(255)
DECLARE @@MyErrorNumber2 INT
FETCH NEXT FROM backupcheck_cursor2
INTO @@DBfailedname2

--Start looping over recordset
WHILE @@FETCH_STATUS = 0
BEGIN

--If most recent transaction log backup is older than 2 days
SELECT @@MESSAGE2 = 'The database "' + @@DBfailedname2 +
'" has no Transaction Log backups within the past 2 days.'
EXEC xp_logevent 60000, @@MESSAGE2, warning
FETCH NEXT FROM backupcheck_cursor2
INTO @@DBfailedname2
--If failures, set the @MyErrorNumber to 2
SET @@MyErrorNumber2 = 2
END

--Close the Cursor stuff
CLOSE backupcheck_cursor2
DEALLOCATE backupcheck_cursor2

--If any errors are found, fail the SQL job
If @@MyErrorNumber = 1 or @@MyErrorNumber = 2
select * from ErrorHandling.dbo.NoSuchTable

GO

fvinagre
Starting Member

29 Posts

Posted - 2004-10-08 : 06:35:00

Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources