Making sure that every database on every server is backed up daily is one of the most important tasks on a DBA's to-do list. But how do you make sure every backup job has run every single day? What if someone has put the job on hold without your knowledge, especially if there are many DBAs on your team managing the environment? You want to make sure every single database has been backed up without relying on any particular backup method, whether it’s native SQL Server backup or a third-party backup tool. The task is more complicated if you have multiple SQL Server systems; multiple sources of schedulers, such as a SQL Server agent and third-party schedulers; and multiple backup methods, such as SQL Server native backup and a third-party backup tool.

That’s why you need to have BI—not business intelligence but backup intelligence. This article shows you how to monitor the backup status of multiple servers from a central server without relying on a scheduler or alternative method, as well as generate a backup report. Note that you shouldn’t rely solely on this method to verify your backup jobs. This report gives only the information that’s stored in SQL Server. You should physically verify your backups for integrity and do test restores whenever possible.

Generating the Backup Status Report

There are multiple ways to monitor and generate comprehensive backup reports. If you don’t have the budget to buy a third-party tool, you can use these scripts to generate a good report on your own. The scripts in this article work with SQL Server 2008, 2005, and 2000, as well as SQL Server 7.0. Note that if you’re managing a multiserver environment, you must designate one SQL Server system as the master server (as shown in Figure 1) where you can create a central database for storing backup information from the monitored linked servers.

The dbo.backupset table in the msdb database contains all the information about the backups. On any given SQL Server system, you can run the script in Listing 1 to find the backup status of all databases that haven’t been backed up for more than seven days. Once you’ve designated the master server, you can follow steps 1-5 to create the stored procedure that will generate the backup status report and the job that executes it.

Step 1: Create linked server connections on the master server. You have to create linked server connections on the master server for all your target servers that you want to monitor. For more information about how to create linked servers in SQL Server 2000, see "How to set up a linked server (Enterprise Manager)" in SQL Server Books Online (BOL). For more information about setting up linked servers in SQL Server 2005, see "Linking Servers" in BOL. The subsequent scripts will use the information from the sysservers table, which contains all the registered linked servers in the master database. Make sure that you can connect to all the linked servers before you proceed.

Step 2: Create the Backup_Status table. Run the script in Listing 2, create_table_backup_status.sql, which creates a table called Backup_Status on the master server. Note that in this listing and all subsequent listings, you have to substitute your database name for .

Step 3: Create the usp_mon_backup_status_of_all_servers stored procedure. Run the script in Web Listing 1 to create the usp_mon_backup_status_of_all_servers stored procedure on the master server. The script is called 02_usp_mon_backup_status_of_all_servers.sql.

Step 4: Create the usp_help_backup_status stored procedure. Use the script in Web Listing 2 to create the usp_help_backup_status stored procedure on the master server.

Step 5: Create a job that executes the usp_help_backup_status stored procedure. Create a job that executes the procedure created in Step 3 on the master server. You can also create a schedule that runs this job daily on the master server. For more information on how to create jobs, job steps, and job schedules, refer to "Implementing Jobs" in BOL.

That’s it, you’re all set. Now every day you just have to connect to the master server using Query Analyzer or SQL Server Management Studio, open a query window, and run the usp_help_backup_status stored procedure shown in Listing 3. Doing so will generate a report that looks similar to Figure 1.

You can create a more sophisticated report by using this stored procedure in Access to create a formatted report, in SQL Server Reporting Services so that others can view the report, or even make the report from the Report Server available on your SharePoint portal. The report generated using this method is equivalent to a report that’s generated by a third-party product.

Verifying Backup Status

You should have a comprehensive knowledge of all the backups on your SQL Server systems, and using this tool can make this important task easy. This method helps you monitor your backups without having to use a SQL Server agent, third-party scheduler, or third-party product.

Listing 1: The Script to Find Databases Not Backed Up in the Past 7 Days

                              SELECT database_name = sd.name, backup_finish_date, type                              FROM master.dbo.sysdatabases sd LEFT OUTER JOIN (SELECT bs.database_name, backup_finish_date,                               type = case type                               when 'D' then 'Database'                              when 'I' then 'Database Differential'                              when 'L' then 'Log'                              when 'F' then 'File or Filegroup'                              end,                               backup_size                              FROM msdb.dbo.backupset bs,                                 (select database_name, max_backup_finish_date = max(backup_finish_date) from msdb.dbo.backupset                              group by database_name) bs1                              where bs.database_name = bs1.database_name                              and bs.backup_finish_date = bs1.max_backup_finish_date) bs3                              ON bs3.database_name = sd.name                              where sd.name not in ('tempdb')                              and                              (backup_finish_date                               

Listing 2

  1. -- Replace your database name for yourdb
  2.  
  3. Use
  4. Go
  5.  
  6. create table Backup_Status
  7. (server_name sysname not null,
  8. database_name sysname not null,
  9. backup_finish_date datetime null,
  10. type varchar(50) null)
  11. Go

Web Listing 1: The Script to Create the usp_mon_backup_status_of_all_servers Stored Procedure

  1. -- Replace your database name for yourdb
  2.  
  3. Use <yourdb>
  4. Go
  5.  
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. SET ANSI_NULLS ON
  9. GO
  10.  
  11. create  procedure usp_mon_backup_status_of_all_servers
  12. as
  13. begin
  14. declare @sql nvarchar(4000)
  15. declare @return_code int
  16. declare @last_backup_date datetime
  17. declare @server_name sysname
  18.  
  19. declare servers_cursor cursor for
  20. select srvname from master.dbo.sysservers
  21. order by srvname
  22.  
  23. delete from backup_status
  24.  
  25. open servers_cursor
  26.  
  27. fetch servers_cursor into @server_name
  28.  
  29.  
  30. while @@fetch_status = 0
  31. begin
  32.  
  33.  
  34.         set @sql = ''
  35.        
  36.         set @sql = 'insert into backup_status SELECT server_name = '''  + @server_name + ''', database_name = convert(varchar, sd.name), backup_finish_date, type ' +
  37.         'FROM [' + @server_name + '].master.dbo.sysdatabases sd LEFT OUTER JOIN
  38.         (SELECT bs.database_name, backup_finish_date,
  39.         type = case type
  40.         when ''D'' then ''Database''
  41.         when ''I'' then ''Database Differential''
  42.         when ''L'' then ''Log''
  43.         when ''F'' then ''File or Filegroup''
  44.         end,
  45.         backup_size
  46.         FROM [' + @server_name + '].msdb.dbo.backupset bs,  
  47.         (select database_name, max_backup_finish_date = max(backup_finish_date) from [' + @server_name + '].msdb.dbo.backupset
  48.         group by database_name) bs1
  49.         where bs.database_name = bs1.database_name
  50.         and bs.backup_finish_date = bs1.max_backup_finish_date) bs3
  51.         ON bs3.database_name = sd.name
  52.         where sd.name not in (''tempdb'')
  53.         and
  54.         (backup_finish_date < getdate() - 7
  55.         or backup_finish_date is null)
  56.         ORDER BY sd.name ASC, backup_finish_date DESC'
  57.  
  58. --      print @sql
  59.  
  60.         exec sp_executesql @sql
  61.  
  62.         fetch servers_cursor into @server_name
  63.  
  64. end
  65.  
  66. close servers_cursor
  67.  
  68. deallocate servers_cursor
  69.  
  70. end
  71.  
  72. GO
  73. SET QUOTED_IDENTIFIER OFF
  74. GO
  75. SET ANSI_NULLS ON
  76. GO

Web Listing 2: The Script to Create usp_help_backup_status Stored Procedure

  1. -- Replace your database name for yourdb
  2.  
  3. Use <yourdb>
  4. Go
  5.  
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. SET ANSI_NULLS ON
  9. GO
  10.  
  11. create  proc  usp_help_backup_status
  12. as
  13. begin
  14. declare @sql nvarchar(4000)
  15. declare @return_code int
  16. declare @last_backup_date datetime
  17. declare @server_name sysname
  18.  
  19. declare servers_cursor cursor for
  20. select distinct server_name from <yourdb>.dbo.backup_status
  21. order by server_name
  22.  
  23. open servers_cursor
  24.  
  25. fetch servers_cursor into @server_name
  26.  
  27. print ''
  28. print '---------------------------------------------------------------------------'
  29. Print 'Databases not backed up in the last seven days'
  30. print '---------------------------------------------------------------------------'
  31. print ''
  32.  
  33. while @@fetch_status = 0
  34. begin
  35.  
  36.  
  37.  
  38.         print '---------------------------------------------------------------------------'
  39.         print 'Server name: ' + @server_name
  40.         print '---------------------------------------------------------------------------'
  41.         print ''
  42.  
  43.         select database_name = convert(varchar, database_name),
  44.         backup_finish_date = convert(varchar(30), backup_finish_date, 121), type = convert(varchar, type)
  45.         from backup_status
  46.         where server_name = @server_name
  47.  
  48.         fetch servers_cursor into @server_name
  49.  
  50. end
  51.  
  52. close servers_cursor
  53.  
  54. deallocate servers_cursor
  55.  
  56. end
  57.  
  58. GO
  59. SET QUOTED_IDENTIFIER OFF
  60. GO
  61. SET ANSI_NULLS ON
  62. GO

Listing 3: The usp_help_backup_status Stored Procedure

  1.  -- Replace your database name for yourdb
  2.  
  3. Use
  4. Go
  5.  
  6. Exec usp_help_backup_status