Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Every DBA Should Know Which Databases are be Backed Up

By Leo Peysakhovich,

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:

Column name

Data type

Description

plan_id

uniqueidentifier

Maintenance plan ID.

plan_name

sysname

Maintenance plan name.

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.

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

Column name

Data type

Description

plan_id

uniqueidentifier

Maintenance plan ID.

database_name

sysname

Name of the database associated with the maintenance plan.

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.

status

Status bits, some of which can be set by the user with sp_dboption (read only, dbo use only, single user, and so on):

1 = autoclose; set with sp_dboption.
4 = select into/bulkcopy; set with sp_dboption.
8 = trunc. log on chkpt; set with sp_dboption.
16 = torn page detection, set with sp_dboption.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with sp_dboption.
1024 = read only; set with sp_dboption.
2048 = dbo use only; set with sp_dboption.
4096 = single user; set with sp_dboption.
32768 = emergency mode.
4194304 = autoshrink.
1073741824 =cleanly shutdown.

Multiple bits can be on at the same time.

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.

Total article views: 13325 | Views in the last 30 days: 12
 
Related Articles
FORUM

Backup Maintenance Plan Error

Backup Maintenance Plan Error

FORUM

Database maintenance plan

Database maintenance plan

FORUM

DataBase Backup for selected tables through SSIS 2005

DataBase Backup for selected tables

BLOG

Why I don’t like maintenance plans

If you create a maintenance plan (SQL 9.0.3042) to backup databases and select All databases as be...

FORUM

Database '' is not valid to be included in the maintenance plan.

Getting "Database '' is not valid to be included in the maintenance plan." during transaction log ba...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones