March 20, 2007 at 10:13 am
Hello all! Looking for some guidance in this problem:
We are an ASP with over 100 customers, many who have a SQL server. With SQL 7.0/2000, we would just create a Maintenance plan for backups and using the wizard, we could send the job history to a central server. From that central server we would run daily reports that were emailed to us so we knew what backed up and what didn't
Well, in 2005, this is obviously gone, so I created a SSIS package that kinda does this, although I am only able to send general information over to the central server. For example, when the package completes, it says just that. I can't find anywhere where I can say that these databases backed up successfully but this one didn't. It' an all or none thing.
Has anyone seen or come up with a good replacement for this scenario? I am getting stuck. Or are there any sys tables that I can query that tell me if they backed up or not?
Thanks for the help!
Mario
March 20, 2007 at 10:26 am
Hello Mario,
You can check these new set of tables to get the information. Please go through 2005 help on this link
http://msdn2.microsoft.com/en-us/library/ms188653.aspx
Hope it gives you the required information.
Thanks
Lucky
March 20, 2007 at 12:26 pm
I created an sp as follows that checks for backup history, minus anything in an exclude list and then emails the result set to our monitoring email account. Maybe this will help out...
USE [DBMonitor]
GO
/****** Object: StoredProcedure [dbo].[spBackupHistoryrpt] Script Date: 03/20/2007 12:24:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[spBackupHistoryrpt]
AS
BEGIN
SET QUOTED_IDENTIFIER OFF
SET NOCOUNT ON
-- DECLARE @myCmd VARCHAR(2000)
CREATE TABLE #T1 (_dbnameVARCHAR(30),
_daycount VARCHAR(20),
_lastday VARCHAR(20)
)
-- populate temp table with last backup day information
INSERT INTO #T1 SELECT LEFT(B.name,30), ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))),'NEVER'), ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') 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 ORDER BY B.name
-- exec @myCmd
SELECT_dbname as "Database Name",
_daycount as "Days Since Last",
_lastday as "Last Day"
from#T1
where_dbname not in (SELECT dbname from dbmonitor.dbo.excludebulist)
orderby _dbname
SELECT" "
SELECTdbname as "Databases Excluded From Backups"
fromdbmonitor.dbo.excludebulist
orderby dbname
-- clean up
DROP TABLE #T1
END
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply