Gerrit Mantel
 |
Programs | SQL_STORED_PROCEDURE - prc_check_database_backup |  |
 |
 |
 |
| Name | prc_check_database_backup |
| Type_desc | SQL_STORED_PROCEDURE |
| Comment | SQLServer Stored Procedure PURPOSE Check database backup state. Check if the last backup of all ONLINE databases are not too old: - The last weekly scheduled Full backup of user databases is max 180 hours old. - The last daily scheduled Diff backup of user databases is max 36 hours old. Als also a recent full backup fulfils. - The last transaction log backup of user databases scheduled with @log_backup_interval_minutes, is max 1.5 times @log_backup_interval_minutes minutes old. Also a recent Full- or Diff backup fulfils. - The last full backup of system databases scheduled with @systemdb_max_hours, is max 1.5 times @systemdb_max_hours hours old. Other checks: - Check if readonly databases are in simple recovery model. Transaction log backups of readonly database get the COPY_ONLY flag and are not in the backupchain and cannot be used for recover actions. - Check if all databases have a "start backup", i.e. begin of backup chain. List the database backup state (in de output grid). On errors: - Send the Error report to email_adddress if @email_address is given. - Save the Error report to text file if @error_report_path is given. PARAMETERS - 1 @log_backup_interval_minutes INT Job schedule interval in minutes for the user databases transaction log backup, 15 / 30 / 60 (default). - 2 @system_backup_interval_hours INT Job schedule interval in hours for the system databases backup, 1 / 2 / 4 (default) / 6 / 8 / 12 / 24. - 3 @error_report_path VARCHAR(255) Path to deliver the error report file. - 4 @email_address NVARCHAR(100) Email address to deliver error report mail. EXAMPLES -- Check database backup state using default backup intervals. EXEC [dbo].[prc_check_database_backup] @error_report_path='C:\Data\MSSQL\error_report', @email_address='g.mantel@gmail.com'; USED OBJECTS - dbo.fun_sqlversion (UDF in local database) - dbo.fun_greatest_date2 (UDF in local database) - dbo.fun_greatest_date3 (UDF in local database) - dbo.fun_FolderExist (UDF in local database) - dbo.prc_table2html (SP in local database) - dbo.prc_save_text_to_utf8file (SP in local database) - msdb.dbo.sp_send_dbmail SQLSERVER CONFIGURATION - OLE Automation has to be enabled. HISTORY 2026-05-12 - Created procedure 2026-05-13 - Always select the database backup state list to the output grid. - Added field Last_backup_type - The @log_backup_interval_minutes minimum is now 15 minutes 2026-05-14 - Changed error_messages TAGS <program> <description>Check database backup state</description> <generic>1</generic> <author>Gerrit Mantel</author> <created>2026-05-12</created> <lastmodified>2026-05-14</lastmodified> </program> |
| Description | Check database backup state |
| Minversion | |
| Generic | 1 |
| Author | Gerrit Mantel |
| Created | 2026-05-12 00:00:00 |
| Lastmodified | 2026-05-14 00:00:00 |
| Xp_cmdshell | 0 |
| Ole_automation | 0 |
| Subprogram_cnt | 11 |
| Sourcecode |  |
| Name | Type desc | Description | Minversion | Generic | Author | Created | Lastmodified | Xp cmdshell | Ole automation |
|---|---|---|---|---|---|---|---|---|---|
| fun_FolderExist | SQL_SCALAR_FUNCTION | Check if folder exists | 1 | Gerrit Mantel | 2015-11-24 00:00:00 | 2026-03-30 00:00:00 | 0 | 1 | |
| fun_format_tablename | SQL_SCALAR_FUNCTION | Format tablename | 1 | Gerrit Mantel | 2025-12-04 00:00:00 | 2025-12-04 00:00:00 | 0 | 0 | |
| fun_getfield | SQL_SCALAR_FUNCTION | Return the n-th field from a given string, between a given separator pattern | 1 | Gerrit Mantel | 2000-01-01 00:00:00 | 2024-02-09 00:00:00 | 0 | 0 | |
| fun_greatest_date2 | SQL_SCALAR_FUNCTION | Returns the greatest of 2 dates | 1 | Gerrit Mantel | 2000-01-01 00:00:00 | 2024-02-09 00:00:00 | 0 | 0 | |
| fun_greatest_date3 | SQL_SCALAR_FUNCTION | Returns the greatest of 3 dates | 1 | Gerrit Mantel | 2018-06-13 00:00:00 | 2024-02-09 00:00:00 | 0 | 0 | |
| fun_nsplit | SQL_TABLE_VALUED_FUNCTION | Split delimited n-string into record values | 1 | Gerrit Mantel | 2005-03-21 00:00:00 | 2024-04-09 00:00:00 | 0 | 0 | |
| fun_sqlversion | SQL_SCALAR_FUNCTION | Returns the sqlversion | 1 | Gerrit Mantel | 2026-03-29 00:00:00 | 2026-03-29 00:00:00 | 0 | 0 | |
| fun_text2html | SQL_SCALAR_FUNCTION | Replace HTML/XML reserved characters in given text to HTML entity names | 1 | Gerrit Mantel | 2014-10-22 00:00:00 | 2025-11-26 00:00:00 | 0 | 0 | |
| fun_uniquename | SQL_SCALAR_FUNCTION | Return an unique name with format yyyy_mm_dd_hhmiss_nnnnnnn | 1 | Gerrit Mantel | 2000-01-01 00:00:00 | 2024-02-09 00:00:00 | 0 | 0 | |
| prc_save_text_to_utf8file | SQL_STORED_PROCEDURE | Write text variable to utf-8 file with BOM | 1 | Gerrit Mantel | 2025-10-12 00:00:00 | 2025-10-12 00:00:00 | 0 | 1 | |
| prc_table2html | SQL_STORED_PROCEDURE | Generate HTML table for a given table or view | 14.0 | 1 | Gerrit Mantel | 2025-11-23 00:00:00 | 2026-02-07 00:00:00 | 0 | 0 |