Every DBA knows that databases should be backed up. SQL Server provides some powerful mechanism for the database backups called ‘Database Maintenance Plan’ to support scheduled full and log backups. It is easy to create a plan to backup all user and system databases. The problem started to appear when the company’s environment changed to having multiple databases where each database may have it's own backup schedule and/or own log backup requirements. Some databases may require backup log files more often then another. Some may not require a log file backup at all. Some environments require many new databases to be created every week. For example, clinical trial or telemarketing companies are often creating one database per client per task. In my role as the DBA, I create 4-10 databases per week. Our company’s SQL Servers keeps 100-400 databases at any given point of time. It makes very difficult to provide manual control for the database backup plans and make sure that all databases are covered by both full and log backup plans.

I’ve decided to create a procedure that will run on daily basis and warn me if there are databases that are not in any full and/or log backup plan. Before I started building such procedure, I did some preparations and made some assumptions:

1. Any backup plan name should have the word ‘backup’ in it’s name

2. Any log backup plan name should have 2 words ‘backup’ and ‘log’ in it’s name

3. All system databases are backed up using a single maintenance plan

I named my plans ‘MAINT Backup User DBs’, ‘MAINT Backup User DB Log’, ‘MAINT Backup Study Databases’, ‘MAINT Backup Non Study Databases’, ‘MAINT Backup System DBs’. 

Before we can see the procedure code lets check the logic and some available system features to solve the problem. The logic is simple. We should know all maintenance plans related to database backups and then check if any database not in the plan for the full backup and, if a database is created for the log backup, check if it belongs to any plan for the log backup.

SQL Server features

1. System table sysdbmaintplans - Contains one row for each database maintenance plan. This table is stored in the msdb database. Select returns information about the specified maintenance plan. We are only interested in 2 columns:

As you remember, the plan name should conform to our conventions. You can use the system stored procedure msdb..sp_help_maintenance_plan as well.

2. System table sysdbmaintplan_databases - Contains one row for each database that has an associated maintenance plan. This table is stored in the msdb database.

The result of the query

select plan_id,database_name
 from msdb..sysdbmaintplan_databases
 plan_id                             database_name
------------------------------------ ----------------------
B688F801-3218-45F6-A7F1-128614737C30 CORPADMIN
B688F801-3218-45F6-A7F1-128614737C30 DBSUPP
019B54B9-0320-4B81-BE6B-14CF287EE073 All System Databases
83C8A354-FBEA-450C-ACB2-7412F8F3A90E CLIENT1
83C8A354-FBEA-450C-ACB2-7412F8F3A90E CLIENT2
656322EB-EF10-4EA5-9FD8-AE4F066708BA CLIENT3
656322EB-EF10-4EA5-9FD8-AE4F066708BA CLIENT4
656322EB-EF10-4EA5-9FD8-AE4F066708BA Northwind
656322EB-EF10-4EA5-9FD8-AE4F066708BA pubs

The plan for all system databases has a keyword ‘All System Databases’.

If one plan for all user databases is created, the keyword 'All User Databases' will be in column database_name.

3.System table sysdatabases - Contains one row for each database on Microsoft SQL Server. When SQL Server is initially installed, sysdatabases contains entries for the master, model, msdb, mssqlweb, and tempdb databases. This table is stored only in the master database.

Different SQL Servers may have various plans based on the type of a server. For example: a development server may need only full database backups once a day, but a production server needs a full backup and log backup plans for some databases and only full backup for another databases. I choose ‘Simple’ database backup model for the user databases that required only full backup and do not require any log backups. (For example: pubs, northwind…). All user databases on development server have ‘Simple’ backup model.

You can define your database settings and choose it based on the field STATUS in the table SYSDATABASES.

Based on the information above, I ’ve created the stored procedure p_db_backup with 1 parameter @recipients varchar(128). This procedure runs as a part of a daily scheduled maintenance plan and every morning I receive a warning report about databases that are not in backup plan. (Better do you job right and never get the report. But from time to time it does happen).

In addition, you can exclude some databases (if you need it) by adding delimited parameter of database names that should be excluded from backup plans

CREATE procedure dbo.p_db_backup
  @recipients varchar(128)
as
BEGIN
SET NOCOUNT ON
declare @plan_nm varchar(128),
        @minid int,
@maxid int,
@plan_id uniqueidentifier,
        @subject varchar(80),
        @message varchar(4000)
declare @tmpplan table ( plan_id uniqueidentifier, plan_name sysname, tid int identity(1,1) )
declare @plandbs table(plan_id uniqueidentifier, plan_name sysname, dbnm varchar(50))
declare @tblp table(database_name varchar(50),tid int identity(1,1))
set @subject = 'Maintenance Jobs to Backup Databases'
set @message = ''
insert into @tmpplan ( plan_id , plan_name )
 select plan_id , plan_name from msdb..sysdbmaintplans
  where plan_name like '%backup%'
insert into @plandbs (plan_id, plan_name, dbnm)
 select sd.plan_id,plan_name,database_name 
  from msdb..sysdbmaintplan_databases sd 
     inner join @tmpplan tp ON sd.plan_id = tp.plan_id
IF ( (select count(dbnm) from @plandbs where dbnm = 'All System Databases') = 0)
 begin
  set @message = @message + 'Maintenance Plan to Backup System Databases is not exists.' 
 end
IF ( ( select count(dbnm) from @plandbs where dbnm = 'All User Databases') = 0)
 BEGIN
   -- start exists test. Check user databases if no general plan
   IF ( ( select count(dbid) from master..sysdatabases where name not in (
              select dbnm from @plandbs where plan_name not like '%backup%log%' ) 
         and name not in ('master','model','msdb','tempdb') ) > 0 )
    begin
      set @message = @message + 'Next Databases are not in the Maintenance Plan for FULL Backup ' 
      insert into @tblp (database_name)
        select name from master..sysdatabases where name not in (
               select dbnm from @plandbs where plan_name not like '%backup%log%' ) 
                           and name not in ('master','model','msdb','tempdb') 
      select @minid = min(tid), @maxid = max(tid)
         from @tblp
      while (@minid <= @maxid)
       begin
         select @message = @message + ' database name: ' + database_name from @tblp 
           where tid = @minid 
         set @minid = @minid + 1
       end
    end
 END 
-- end of EXISTS TEST
-- verify log backup
IF ( ( select count(dbid) from master..sysdatabases where name not in (
        select dbnm from @plandbs where plan_name like '%backup%log%' ) 
      and name not in ('master','model','msdb','tempdb')
      and status = 16 ) > 0 )
 begin
   set @message = @message + 'Next Databases are not in the Maintenance Plan for LOG Backup.' 
   insert into @tblp (database_name)
      select name from master..sysdatabases
     where name not in (
            select dbnm from @plandbs where plan_name like '%backup%log%' ) 
         and name not in ('master','model','msdb','tempdb')
 and status = 16
  select @minid = min(tid), @maxid = max(tid) from @tblp
  while (@minid <= @maxid)
   begin
     select @message = @message + ' database name: ' + database_name
      from @tblp
      where tid = @minid 
 
     set @minid = @minid + 1
   end
 end
IF ( @message <> '' ) 
 begin
  exec master..xp_sendmail @recipients = @recipients,
    @subject = @subject,
    @message = @message
 end

Conclusion

As you can see, some conditions, parameters, and assumptions can be changed based on DBA’s preferences and existing system standards, but the idea will remain. It is a handy way especially in environments where databases are created often. Even if your environment is not changing very often and the server has a small number of databases it would prevent you from accidental database backup plan changes and leaving some databases without backups. I have been using this logic for over a year now and have no problems with SQL Server 7 and SQL Server 2000.