-- Stored Procedure: dbo.GetBackupInfo -- Bill Wunder use admin GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetBackupInfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[GetBackupInfo] GO CREATE PROCEDURE [dbo].[GetBackupInfo] @BackupScheduleSet varchar(30) , @Recipients varchar(100) AS /******************************************************************************************************* * admin.dbo.GetBackupInfo * Creator: Bill Wunder * Date: 02-03-2003 * * Project: Backup/Restore * Project Mgr: * Dev Contact: * * Description: produce a single result set of the local backup plans where this server is the BackupServer * Notes: If any user databases (or master, model or msdb) are found without BackupConfig row or if a row * is found for a database on this server taht does not exist on the server an email will be sent * * Usage: EXECUTE admin.dbo.GetBackupInfo 'Do Not Backup', 'bill.wunder@wallst.com' * * * Modifications: * Developer Name Date Brief Description * ------------------ -------- ------------------------------------------------------------ * bw 4-01-03 just send email if a user database has no plan rather * than default to a daily full backup * bw 5-30-03 just send email if a user database no longer exists * sn 7/15/03 removed the reference to 'Daily Full Backup' on the notification for missing plans * bw 11-03-03 make this procedure affect SQL Server instance it runs from only * even if rows from another server are in the table (to support FB machines) ********************************************************************************************************/ --------------------------------------------- -- declare variables --------------------------------------------- declare @PrimaryServer varchar(128), @FallbackServer varchar(128), @NewDatabasesCount int, @Query varchar(1000) --------------------------------------------- -- create temp tables --------------------------------------------- --------------------------------------------- -- set session --------------------------------------------- SET NOCOUNT ON --------------------------------------------- -- body of stored procedure --------------------------------------------- -- email a list of any databases without a backup plan to DBOps if this is the if exists (select name from master.dbo.sysdatabases where name not in (select DatabaseName from admin.dbo.BackupConfig where BackupServerName = @@servername) and name not in ('pubs','Northwind','tempdb')) begin set @Query = 'select name from master.dbo.sysdatabases ' + 'where name not in (select DatabaseName from admin.dbo.BackupConfig ' + 'where BackupServerName = @@servername) ' + 'and name not in (''pubs'',''Northwind'',''tempdb'')' exec sysmon.dbo.safe_sendmail @recipients = @Recipients , @subject = 'Databases not being backed up.' , @message = 'The following databases do not have a backup plan in admin.dbo.BackupConfig' , @query = @Query end -- email a list of non existant dbs that have a backup plan -- make sure only rows for local dbs are considered If exists (select DatabaseName from admin.dbo.BackupConfig where db_id(DatabaseName) is null and BackupServerName = @@servername) begin set @Query = 'select DatabaseName from admin.dbo.BackupConfig ' + 'where db_id(DatabaseName) is null ' + 'and BackupServerName = @@servername' exec sysmon.dbo.safe_sendmail @recipients = 'DBOps@wallst.com', @subject = 'Backup plans with no associated database on server.', @message = 'The following databases do not exist on this server yet have a backup plan in admin.dbo.BackupConfig', @query = @Query end select DatabaseName, BackupServerName, IncludeRestore, RestoreServerName, BeforeBackupPackageName, BeforeBackupPackageUNCFile, BeforeBackupPackageGlobalVariables, ArchivePackageName, ArchivePackageUNCFile, ArchivePackageGlobalVariables, BackupPackageName, BackupPackageUNCFile, BackupPackageGlobalVariables, AfterBackupPackageName, AfterBackupPackageUNCFile, AfterBackupPackageGlobalVariables, BeforeRestorePackageName, BeforeRestorePackageUNCFile, BeforeRestorePackageGlobalVariables, RestorePackageName, RestorePackageUNCFile, RestorePackageGlobalVariables, AfterRestorePackageName, AfterRestorePackageUNCFile, AfterRestorePackageGlobalVariables from admin.dbo.BackupConfig where BackupScheduleSet = @BackupScheduleSet and BackupServerName = @@servername GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO