Programs

SQL_STORED_PROCEDURE - prc_backup_userdb_log

Main program

Name	prc_backup_userdb_log
Type_desc	SQL_STORED_PROCEDURE
Comment	SQLServer Stored Procedure PURPOSE Make transaction log backup of all user databases. Only user databases are backupped. Only databases with status ONLINE are backupped. Database in recovery model SIMPLE are skipped. Logshipping primary databases are skipped (they have separate LS_Backup jobs for log backups). Logshipping secondary databases are skipped. HADR databases are log backupped only on preferred backup replica. Backup is made to a subdir (or not) of the given @backup_path (with name {dbname}), depending of parameter @subdir (default is backup to subdir). Backup is followed by a restore with verifyonly depending on parameter @verify (default is backup without verify). A full backup of a database is made when a log backup cannot be made. By default, force a full backup of a database when no full backup is found in the msdb backup history (made by this server, no COPY_ONLY type). This can be disabled with @force_full_backup=0. When a database is restored from another database, technically a log backup can be made, but then the backup history of the new database can be confusing. PARAMETERS - 1 @backup_path VARCHAR(265) Backup_path - 2 @subdir BIT 0 / 1 (default) Optional: Backup to subdir 0/1. - 3 @verify BIT 0 (default) / 1 Optional: Backup with verify 0/1. - 4 @force_full_backup BIT 0 / 1 (default) Optional: Force a full backup when there is no full backup found in the msdb history 0/1. EXAMPLES -- Transaction log backups with standard parameters EXEC [dbo].[prc_backup_userdb_log] @backup_path = 'C:\Data\MSSQL\backup'; -- Transaction log backups with verify EXEC [dbo].[prc_backup_userdb_log] @backup_path = 'C:\Data\MSSQL\backup', @verify=1; -- Transaction log backups with force_full_backup EXEC [dbo].[prc_backup_userdb_log] @backup_path = 'C:\Data\MSSQL\backup', @force_full_backup=1; USED OBJECTS - prc_backup_log (Stored_procedure in local database) - dbo.fun_FolderExist (UDF in local database) - dbo.fun_sqlversion (UDF in local database) SQLSERVER CONFIGURATION - OLE Automation has to be enabled. HISTORY 2019-10-29 - Created procedure 2020-02-18 - Removed LOCAL_BACKUP_SKIP table 2020-02-20 - Introduced parm skip_backup in CONFIGURATION to provide a list of databases to skip the backup 2020-07-08 - Replaced table LOCAL_BACKUP_CONFIG by CONFIGURATION - Backup_path_hadr is obsolete now 2020-11-03 - Replaced table CONFIGURATION into SETUP_CONFIG 2020-11-04 - Check on parameter backup_base in stead of local_backup to continue 2021-12-14 - SETUP_CONFIG parameter verify_backup is renamed to backup_with_verify 2022-07-27 - Fixed bug: wrong fieldname used for table SETUP_SKIP_BACKUP 2023-05-12 - Removed @hadr_is_primary_replica from cursor 2023-12-07 - Made suitable for DBMAINT 2023-12-09 - Changed backup path 2024-02-11 - Changed table SETUP to Setup - Use dbo.fun_setup2value to read Setup values 2024-04-19 - Extend backup_path with servername 2024-04-23 - Don't extend backup_path anymore 2024-07-23 - Adjusted Setup field lengths and types 2025-05-18 - Renamed table Setup to Ref_setup 2026-03-29 - Removed dynamic SQL for cursor - Minimum version 12.0 2026-04-02 - Made procedure full configurable with parameters 2026-04-03 - Changed pararameters for call to prc_backup_log 2026-04-08 - Make full backup if there is no last_log_backup_lsn 2026-04-29 - Added version check 2026-05-03 - Added parameter @force_full_backup 2026-05-12 - Renamed procedure from prc_backup_all_userdb_log to prc_backup_userdb_log 2026-05-13 - Changed parameter @force_full_backup default to 1 TAGS <program> <description>Make transaction log backup of all user databases</description> <generic>1</generic> <minversion>12</minversion> <author>Gerrit Mantel</author> <created>2019-10-29</created> <lastmodified>2026-05-13</lastmodified> </program>
Description	Make transaction log backup of all user databases
Minversion	12.0
Generic	1
Author	Gerrit Mantel
Created	2019-10-29 00:00:00
Lastmodified	2026-05-13 00:00:00
Xp_cmdshell	0
Ole_automation	0
Subprogram_cnt	5
Sourcecode

Sub programs