Programs

SQL_STORED_PROCEDURE - prc_backup_userdb_diff

Main program

Name	prc_backup_userdb_diff
Type_desc	SQL_STORED_PROCEDURE
Comment	SQLServer Stored Procedure PURPOSE Make differential backup of all user databases. Only user databases are backupped. Only databases with status ONLINE are backupped. Logshipping secondary databases are skipped. HADR databases not on primary replica are skipped. 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 diffential 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 differential 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 -- Differential backups with standard parameters EXEC [dbo].[prc_backup_userdb_diff] @backup_path = 'C:\Data\MSSQL\backup'; -- Differential backups with verify EXEC [dbo].[prc_backup_userdb_diff] @backup_path = 'C:\Data\MSSQL\backup', @verify=1; USED OBJECTS - prc_backup_database (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 2014-07-29 - Created procedure 2015-07-08 - Added logics for HADR databases 2015-07-14 - Added parameter @dbname 2015-07-15 - Error text changed for database not on primary replica 2015-07-28 - Changed error messages 2016-01-19 - Changed variable @verify_backup to type BIT - Read variable @local_backup and skip backups if 0 (and @dbname is not given) 2018-01-16 - Variable @local_backup is obsolete now 2020-02-18 - Removed table LOCAL_BACKUP_SKIP 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 - Take full backup when database needs full backup (only when parameter @dbname <> '') - 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-28 - 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_database 2026-04-29 - Added version check 2026-05-12 - Renamed procedure from prc_backup_all_userdb_diff to prc_backup_userdb_diff 2026-05-13 - Added parameter @force_full_backup (with default 1) 2026-05-15 - Bug fix: Fixed error "Cursorfetch: The number of variables declared in the INTO list must match that of selected columns." TAGS <program> <description>Make differential backup of all user databases</description> <generic>1</generic> <minversion>12</minversion> <author>Gerrit Mantel</author> <created>2014-07-29</created> <lastmodified>2026-05-15</lastmodified> </program>
Description	Make differential backup of all user databases
Minversion	12.0
Generic	1
Author	Gerrit Mantel
Created	2014-07-29 00:00:00
Lastmodified	2026-05-15 00:00:00
Xp_cmdshell	0
Ole_automation	0
Subprogram_cnt	4
Sourcecode

Sub programs