Programs

SQL_STORED_PROCEDURE - prc_backup_userdb_full

Main program

Name	prc_backup_userdb_full
Type_desc	SQL_STORED_PROCEDURE
Comment	SQLServer Stored Procedure PURPOSE Make full 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). Backupfiles are splitted depending on parameter @split (default is no splitting). The number of parts the backup is splitted to is calculated from database size and number of datafiles using following rules: - if nr_of_datafiles >= 8 AND db_size >= 4000000 mb, split parts = 8 - elseif nr_of_datafiles >= 4 AND db_size >= 2000000 mb, split parts = 4 - elseif nr_of_datafiles >= 2 AND db_size >= 1000000 mb, split parts = 2 - else no splitting Backupfiles are deleted if older than the given number of days by parameter @keep_days. 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 @split BIT 0 (default) / 1 Optional: Split backupfiles 0/1 when database is large. - 5 @keep_days INT 90 (default) / n / 0 Optional: Delete old backupfiles older than n days. Value 0 or negative means no deletes. EXAMPLES -- Full backups with standard parameters EXEC [dbo].[prc_backup_userdb_full] @backup_path = 'C:\Data\MSSQL\backup'; -- Full backups with verify EXEC [dbo].[prc_backup_userdb_full] @backup_path = 'C:\Data\MSSQL\backup', @verify=1; -- Full backups with standard parameters, don't delete old backup files EXEC [dbo].[prc_backup_userdb_full] @backup_path = 'C:\Data\MSSQL\backup', @keep_days=0; USED OBJECTS - prc_backup_database (Stored_procedure in local database) - dbo.fun_FolderExist (UDF in local database) - dbo.fun_sqlversion (UDF in local database) - master.dbo.xp_delete_file (Extended stored procedure) SQLSERVER CONFIGURATION - OLE Automation has to be enabled. HISTORY 2014-07-29 - Created procedure 2015-05-11 - Changed delete period from 15 to 30 days 2015-05-28 - Changed delete period from 30 to 15 days 2015-07-08 - Added logics for HADR databases - Added parameter @dbname 2015-07-14 - Minor change in remark text. 2015-07-15 - Error text changed for HADR database not on primary replica 2015-07-28 - Changed error messages 2016-01-19 - Changed variable @backup_with_verify to type BIT - Read variable @local_backup and skip backups if 0 (and @dbname is not given) 2016-02-01 - Removed delete backupfiles action 2016-03-03 - Split parts values can only be 2 or 4 2016-07-13 - Read variable @backup_keep_days and delete backupfiles if > 0 (and @dbname is not given) 2016-07-25 - Remove old backupfiles is now performed after backup 2017-08-21 - Split parts values can be 2,4 or 8 (new possible value) 2018-01-16 - Variable @local_backup is obsolete now 2019-12-12 - Table LOCAL_BACKUP_SPLIT is obsolete now. Variable @parts is calculated with formula. 2019-12-18 - Bug fixed. Large database with filestream type datafiles did not split. 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 keep_days is renamed to backup_keep_days - SETUP_CONFIG parameter verify_backup is renamed to backup_with_verify 2022-07-12 - Fixed bug: wrong backup_dir set when one database has more parts than 1 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-18 - Fixed bug: Delete old backupfiles after @backup_keep_days did not work 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-01 - Made procedure full configurable with parameters 2026-04-03 - Changed pararameters for call to prc_backup_database 2026-04-08 - Fixed bug: Split does not work properly 2026-04-29 - Added version check 2026-05-12 - Renamed procedure from prc_backup_all_userdb_full to prc_backup_userdb_full TAGS <program> <description>Make full backup of all user databases</description> <generic>1</generic> <minversion>12</minversion> <author>Gerrit Mantel</author> <created>2014-07-29</created> <lastmodified>2026-05-12</lastmodified> </program>
Description	Make full backup of all user databases
Minversion	12.0
Generic	1
Author	Gerrit Mantel
Created	2014-07-29 00:00:00
Lastmodified	2026-05-12 00:00:00
Xp_cmdshell	0
Ole_automation	0
Subprogram_cnt	4
Sourcecode

Sub programs